求一条查许语句

Question

现有3张表
1.user    
id       username
1        小汪
2        小张
2.定购表
id       userid     price    productid
1          1         1000       3
2          1         200        4
3          1         400        4 
4          2         400        3 
5          2         3000       4
3.产品表
productid    product
3             玩具
4             茶杯
我现在想得到的查询结果是:按客户定购的总金额排序.比如
小张       玩具        400
小张       茶杯       3000
                 合计:     3400
小汪      玩具        1000
小汪      茶杯        200
小汪      茶杯        400
                合计:1600
象这样查询,sql语句该怎么写啊.

Answer 1

--實現大部分，不過還差一步，先貼出來Create Table [user]
(id Int,
 username Nvarchar(10))
Insert [user] Select 1,        N'小汪'
Union All Select 2,        N'小张'
Create Table 定购表
(id Int,
 userid Int,
 price Int,
 productid Int)
Insert 定购表 Select 1,          1,         1000,       3
Union All Select 2,          1,         200,        4
Union All Select 3,          1,         400,        4 
Union All Select 4,          2,         400,        3 
Union All Select 5,          2,         3000,       4
Create Table 产品表
(productid Int,
 product Nvarchar(20))
Insert 产品表 Select 3,             N'玩具'
Union All Select 4,             N'茶杯'
GO
Select 
(Case When Grouping(C.product) = 1 Then N'合计' Else B.username End) As username,
C.product,
SUM(price) As price
From
定购表 A
Left Join
[user] B
On A.userid = B.id
Inner Join
产品表 C
On A.productid = C.productid
Group By
B.username,
C.product
With Rollup
Having username Is Not Null
GO
Drop Table [user], 定购表, 产品表
--Result
/*
username product price
小汪 玩具 1000
小汪 茶杯 600
合计 NULL 1600
小张 玩具 400
小张 茶杯 3000
合计 NULL 3400
*/

Answer 2

ＳＱＬ２００５中
select b.username,c.product,a.price
from 定购表 a (nolock)
inner join [user] b on a.id=b.id
inner join 产品表 c on c.productid=b.productid
group by b.username,c.product
with rollup 
having 
grouping(b.username)=0  or 
grouping(c.product)=1

Answer 3

--或者用下面的Declare @user table (id int,UserName varchar(8))
Insert into @User
 Select 1        ,'小汪'
Union Select 2   ,'小张' Declare @定购表 table (id int,userid int,price int,productid int) 
Insert into @定购表
 Select 1          ,1        , 1000 ,       3
Union Select 2          ,1   , 200 ,        4
Union Select 3         , 1   , 400 ,        4 
Union Select 4          ,2   , 400 ,        3 
Union Select 5          ,2   , 3000 ,       4 Declare @产品表 table (productid int,product varchar(8))
Insert into @产品表
Select 3            ,'玩具'
Union Select 4      ,'茶杯' 
 
Select (Case when (grouping(a.UserName)=1) or
                  (grouping(c.product)=1) then '' else a.UserName end) as UserName,
       (Case when grouping(a.UserName)=1 then '' 
                  else IsNULL(c.product,'合计:') end)  as product,
       sum(b.price) as price
from @User as a inner Join @定购表 as b on a.id=b.userid
                inner Join @产品表 as c on c.productid=b.productid
group by a.UserName, c.product
with Cube having grouping(a.UserName)<>1

Answer 4

declare @user   table(id int,username nvarchar(5))
insert @user(id,username)values(1,'小汪')
insert @user(id,username)values(2,'小张')declare @定购表 table(id  int,userid int,price int,productid int)
insert @定购表(id,userid,price,productid)values(1,1,1000,3)
insert @定购表(id,userid,price,productid)values(2,1,200,4)
insert @定购表(id,userid,price,productid)values(3,1,400,4)
insert @定购表(id,userid,price,productid)values(4,2,400,3)
insert @定购表(id,userid,price,productid)values(5,2,3000,4)declare @产品表 table(productid int,product nvarchar(10))
insert @产品表(productid,product)values(3,'玩具')
insert @产品表(productid,product)values(4,'茶杯')
select case IsSum when 1 then '' else username end,Product,Price from 
(select id,username,SumPrice from @user as tbUser left join (select userid,sum(Price) 'SumPrice' from @定购表 group by userid) 定购合计 on tbUser.id=定购合计.userid) as tbUserleft join(
select userid,0 IsSum,product,price from @定购表 as 定购表 left join @产品表 as 产品表 on 定购表.productid=产品表.productid
union
select userid,1 IsSum,'合计',sum(price) 'price' from @定购表 as 定购表 group by userid
) as 定购表
on tbUser.id=定购表.Userid
order by SumPrice desc,UserName,IsSum

Answer 5

--用with cube 如WangZWang(先来) 的
--若用with rollup,則如下：
Declare @user table (id int,UserName nvarchar(8))
Insert into @User
 Select 1        ,'小汪'
Union Select 2   ,'小张' Declare @定购表 table (id int,userid int,price int,productid int) 
Insert into @定购表
 Select 1          ,1        , 1000 ,       3
Union Select 2          ,1   , 200 ,        4
Union Select 3         , 1   , 400 ,        4 
Union Select 4          ,2   , 400 ,        3 
Union Select 5          ,2   , 3000 ,       4 Declare @产品表 table (productid int,product nvarchar(8))
Insert into @产品表
Select 3            ,'玩具'
Union Select 4      ,'茶杯' --查詢
select case when grouping(c.product)=1 and grouping(b.username)=0 then '' else isnull(b.username,'') end as username
,case when grouping(c.product)=1 and grouping(b.username)=0 then N'小計:'
  when grouping(b.username)=1 then N'合計:' else isnull(c.product,'') end
 as product
,sum(a.price) as price
from @定购表 a  
left join @User b on a.userid=b.id
left join @产品表 c on c.productid=a.productid
group by b.username,c.product 
with rollup  /*
結果
小汪 玩具 1000
小汪 茶杯 600
小計: 1600
小张 玩具 400
小张 茶杯 3000
小計: 3400
合計: 5000
*/

Answer 6

if object_id('pubs..[user]') is not null
   drop table [user]
go
create table [user](id int,username varchar(10))
insert into [user](id,username) values(1,        '小汪')
insert into [user](id,username) values(2,        '小张')
goif object_id('pubs..定购表') is not null
   drop table 定购表
go
create table 定购表(id int,userid int,price int,productid int)
insert into 定购表(id,userid,price,productid) values(1,          1,         1000,       3)
insert into 定购表(id,userid,price,productid) values(2,          1,         200 ,       4)
insert into 定购表(id,userid,price,productid) values(3,          1,         400 ,       4)
insert into 定购表(id,userid,price,productid) values(4,          2,         400 ,       3)
insert into 定购表(id,userid,price,productid) values(5,          2,         3000,       4)
goif object_id('pubs..产品表') is not null
   drop table 产品表
go
create table 产品表(productid int,product varchar(10))
insert into 产品表(productid,product) values(3,             '玩具')
insert into 产品表(productid,product) values(4,             '茶杯')
goselect isnull(username,'合计') username,isnull(product,'合计') product,sum(price) price from
(
  select a.username,c.product,b.price from [user] a,定购表 b,产品表 c
  where a.id = b.userid and b.productid = c.productid
) t
group by username,product with rollup
order by case username when '合计' then 1 else 0 end ,case product when '合计' then 1 else 0 enddrop table [user],定购表,产品表/*
username   product    price       
---------- ---------- ----------- 
小汪         茶杯         600
小汪         玩具         1000
小汪         合计         1600
小张         茶杯         3000
小张         玩具         400
小张         合计         3400
合计         合计         5000（所影响的行数为 7 行）
*/