表1
A B C D E
1 2 0 4 5
1 2 0 4 5
1 2 1 4 5
1 3 0 7 8
1 3 1 7 8
1 3 1 7 8
生成表2
A B C D E F
从表2中如何得到
A B D E F0 F1
1 2 4 5 2 1
1 3 7 8 1 2
---select *,count(1) as cnt
into #
from table1
group by a,b,c,d,e
select a,b,d,e,
f0 = f,
f1 =identity(int,1,1)
into #2
from #
select * from #2
A B C D E
1 2 0 4 5
1 2 0 4 5
1 2 1 4 5
1 3 0 7 8
1 3 1 7 8
1 3 1 7 8
生成表2
A B C D E F
从表2中如何得到
A B D E F0 F1
1 2 4 5 2 1
1 3 7 8 1 2
---select *,count(1) as cnt
into #
from table1
group by a,b,c,d,e
select a,b,d,e,
f0 = f,
f1 =identity(int,1,1)
into #2
from #
select * from #2
f1 =identity(int,1,1) 这两个何解?
SElECT …… F(真实字段名) AS F0(字段别名)
, identity(int,1,1) (字段公式) AS f1(字段别名)……
From
(select a.S_Adid ,Expr1,Expr2 From
(SELECT S_Adid, SUM(S_PV) AS Expr1
FROM AdRm_Statistics
WHERE (S_Open = 0)
GROUP BY S_Adid) a,
(SELECT S_Adid, SUM(S_PV) AS Expr2
FROM AdRm_Statistics
WHERE (S_Open = 1)
GROUP BY S_Adid) b
Where a.S_Adid = b.S_Adid ) c 没办法。只能用最笨的方法老。现在有个问题是
select *
From
(select a.S_Adid ,Expr1,Expr2 From
(SELECT S_Adid, SUM(S_PV) AS Expr1
FROM AdRm_Statistics
WHERE (S_Open = 0)
GROUP BY S_Adid) a,
(SELECT S_Adid, SUM(S_PV) AS Expr2
FROM AdRm_Statistics
WHERE (S_Open = 1)
GROUP BY S_Adid) b
Where a.S_Adid = b.S_Adid ) c left join AdRm_Statistics d on c.S_Adid = d.S_Adid //这个加不上去。总是把所有数据都显示出来。
那里错了?
FROM (SELECT a.S_Adid, Expr1, Expr2
FROM (SELECT S_Adid, SUM(S_PV) AS Expr1
FROM AdRm_Statistics
WHERE (S_Open = 0)
GROUP BY S_Adid) a,
(SELECT S_Adid, SUM(S_PV) AS Expr2
FROM AdRm_Statistics
WHERE (S_Open = 1)
GROUP BY S_Adid) b
WHERE a.S_Adid = b.S_Adid) c INNER JOIN
(SELECT DISTINCT S_Adid, S_Url_Site, S_Datetime
FROM AdRm_Statistics) d ON c.S_Adid = d.S_Adid最后的结果。~:( 这代码一点技术含量都没。但真的找不到好方法老。
into #
from table1
group by a,b,c,d,e
select a,b,d,e,
f0 = f,
f1 =identity(int,1,1)
into #2
from #
select * from #2
在
group by a,b,c,d,e 这个问题上比较麻烦。因为只对.S_Adid。
导致后面的f0 = f,
f1 =identity(int,1,1)
的数据不对