linq 行转列
程序员文章站
2022-06-10 15:11:17
...
表结构如下:
- id NAME result
- ----------- -------------------- ------
- 1 jim 胜
- 2 jim 胜
- 3 jim 负
- 4 Tom 胜
- 5 Tom 负
- 6 Sam 负
- 7 Sam 负
想得到的结果是:
- NAME 胜 负
- -------------------- ----------- -----------
- jim 2 1
- Sam 0 2
- Tom 1 1
SQL可以这样来实现:
- SELECT t1.name,
- (SELECTCOUNT(1)FROM temp t2 WHERE t2.NAME=t1.NAME AND t2.result='胜')AS'胜',
- (SELECTCOUNT(1)FROM temp t3 WHERE t3.NAME=t1.NAME AND t3.result='负')AS'负'
- FROM
- (SELECT NAME FROM temp GROUPBY NAME ) AS t1
- name 胜 负
- -------------------- ----------- -----------
- jim 2 1
- Sam 0 2
- Tom 1 1
- (3row(s) affected)
用LINQ怎么来写呢?
- var query=from t in Temps
- group t by t.NAMEinto m
- selectnew
- {
- NAME=m.Key,
- 胜=m.Count(n=>n.Result=="胜"),
- 负=m.Count(n=>n.Result=="负")
- };
这是LINQ翻译成的SQL语句:
- -- Region Parameters
- DECLARE
- @p0 NVarChar(1) = N''
- DECLARE
- @p1 NVarChar(1) = N''
- -- EndRegion
- SELECT
- [t1]
- [NAME], (
- SELECT COUNT(*)
- FROM [temp] AS
- [t2]
- WHERE ([t2]
- [result] = @p0) AND ((([t1]
- [NAME] IS NULL) AND ([t2]
- [NAME] IS NULL)) OR (([t1]
- [NAME] IS NOT NULL) AND ([t2]
- [NAME] IS NOT NULL) AND ([t1]
- [NAME] = [t2]
- [NAME])))
- ) AS [], (
- SELECT COUNT(*)
- FROM [temp] AS
- [t3]
- WHERE ([t3]
- [result] = @p1) AND ((([t1]
- [NAME] IS NULL) AND ([t3]
- [NAME] IS NULL)) OR (([t1]
- [NAME] IS NOT NULL) AND ([t3]
- [NAME] IS NOT NULL) AND ([t1]
- [NAME] = [t3]
- [NAME])))
- ) AS
- []
- FROM
- (
- SELECT [t0]
- [NAME]
- FROM [temp] AS
- [t0]
- GROUP BY [t0]
- [NAME]
- ) AS
- [t1]