着急问阿，在线等啊！！！！SQL问题
我现在的SQL是这样的：
SELECT Operator, Flag, SUM(TotalMoney) AS TotalMoney, COUNT(*) AS COUNTS
FROM PawnM
WHERE (FromDate > = '2003-01-1 ') AND (ToDate <= '2007-7-17 ')
GROUP BY Operator, Flag
查询结果：
Operator Flag TotalMoney Counts
操作员1 0 100 12
操作员2 1 200 13
操作员1 1 300 14
操作员1 2 400 15
我想实现的查询结果如下：
Operator TotalMoney Counts TotalMoney2 Counts2 TotalMoney3 Counts3
操作员1 0 100 300 14 400 15
操作员2 1 200 0 0 0 0
Flag：0--9
“TotalMoney2 Counts2 TotalMoney3 Counts3 ”这些字段PawnM表中没有
请问在一个SQL语句中能实现吗？怎么写？
[解决办法]
SELECT Operator, SUM(TotalMoney) AS TotalMoney1, COUNT(*) AS COUNTS1,
' ' AS TotalMoney2, ' ' AS COUNTS2,
' ' AS TotalMoney3, ' ' AS COUNTS3
FROM PawnM
WHERE (FromDate > = '2003-01-1 ') AND (ToDate <= '2007-7-17 ') anf flag =0
GROUP BY Operator, Flag
union
SELECT Operator, ' ' as TotalMoney1, ' 'AS COUNTS1,
SUM(TotalMoney) AS TotalMoney2, COUNT(*) AS COUNTS2,
' ' AS TotalMoney3, ' ' AS COUNTS3
FROM PawnM
WHERE (FromDate > = '2003-01-1 ') AND (ToDate <= '2007-7-17 ') anf flag =1
GROUP BY Operator, Flag
union
SELECT Operator, ' ' as TotalMoney1, ' 'AS COUNTS1,// 不行的话，把 ' '改为0
' ' AS TotalMoney2, ' ' AS COUNTS2
SUM(TotalMoney) AS TotalMoney3, COUNT(*) AS COUNTS3,
FROM PawnM
WHERE (FromDate > = '2003-01-1 ') AND (ToDate <= '2007-7-17 ') anf flag =2
GROUP BY Operator, Flag
union
...
就这样写，自己试试
[解决办法]
select Operator, SUM(TotalMoney1) AS TotalMoney1, COUNT(COUNTS1) AS COUNTS1,
SUM(TotalMoney2) AS TotalMoney2, COUNT(COUNTS2) AS COUNTS2,
SUM(TotalMoney3) AS TotalMoney3, COUNT(COUNTS23) AS COUNTS3
from
(
SELECT Operator, SUM(TotalMoney) AS TotalMoney1, COUNT(*) AS COUNTS1,
' ' AS TotalMoney2, ' ' AS COUNTS2,
' ' AS TotalMoney3, ' ' AS COUNTS3
FROM PawnM
WHERE (FromDate > = '2003-01-1 ') AND (ToDate <= '2007-7-17 ') anf flag =0
GROUP BY Operator, Flag
union
SELECT Operator, ' ' as TotalMoney1, ' 'AS COUNTS1,
SUM(TotalMoney) AS TotalMoney2, COUNT(*) AS COUNTS2,
' ' AS TotalMoney3, ' ' AS COUNTS3
FROM PawnM
WHERE (FromDate > = '2003-01-1 ') AND (ToDate <= '2007-7-17 ') anf flag =1
GROUP BY Operator, Flag
union
SELECT Operator, ' ' as TotalMoney1, ' 'AS COUNTS1,// 不行的话，把 ' '改为0
' ' AS TotalMoney2, ' ' AS COUNTS2
SUM(TotalMoney) AS TotalMoney3, COUNT(*) AS COUNTS3,
FROM PawnM
WHERE (FromDate > = '2003-01-1 ') AND (ToDate <= '2007-7-17 ') anf flag =2
GROUP BY Operator, Flag
union
...
) as table1
where
...
看这个
[解决办法]
http://community.csdn.net/Expert/topic/5400/5400771.xml?temp=.5636865
[解决办法]
我想实现的查询结果如下：
Operator TotalMoney Counts TotalMoney2 Counts2 TotalMoney3 Counts3
操作员1 0 100 300 14 400 15
操作员2 1 200 0 0 0 0
Flag：0--9
---------
樓主的結果有些問題
Flag：0--9，那麼結果應該有2 * 10 + 1 = 21列。
但是樓主的結果中的第二列怎麼是0，1？
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(Operator varchar(10),Flag varchar(1),TotalMoney int,Counts int)
insert into tb(Operator,Flag,TotalMoney,Counts) values( '操作员1 ', '0 ', 100, 12)
insert into tb(Operator,Flag,TotalMoney,Counts) values( '操作员2 ', '1 ', 200, 13)
insert into tb(Operator,Flag,TotalMoney,Counts) values( '操作员1 ', '1 ', 300, 14)
insert into tb(Operator,Flag,TotalMoney,Counts) values( '操作员1 ', '2 ', 400, 15 )
go
declare @sql varchar(8000)
set @sql = 'select Operator '
select @sql = @sql + ' , max(case flag when ' ' ' + flag + ' ' ' then totalmoney else 0 end) [ ' + 'totalmoney '+flag + '] '
+ ' , max(case flag when ' ' ' + flag + ' ' ' then counts else 0 end) [ ' + 'counts '+flag + '] '
from (select distinct flag from tb) as a
set @sql = @sql + ' from tb group by Operator '
exec(@sql)
drop table tb
/*
Operator totalmoney0 counts0 totalmoney1 counts1 totalmoney2 counts2
---------- ----------- ----------- ----------- ----------- ----------- -----------
操作员1 100 12 300 14 400 15
操作员2 0 0 200 13 0 0
*/
