求一条查许语句
现有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语句该怎么写啊.
[解决办法]
--實現大部分，不過還差一步，先貼出來
Create Table [user]
(idInt,
usernameNvarchar(10))
Insert [user] Select 1, N '小汪 '
Union All Select 2, N '小张 '
Create Table 定购表
(idInt,
useridInt,
priceInt,
productidInt)
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 产品表
(productidInt,
productNvarchar(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
/*
usernameproductprice
小汪玩具1000
小汪茶杯600
合计NULL1600
小张玩具400
小张茶杯3000
合计NULL3400
*/
[解决办法]
ＳＱＬ２００５中
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
[解决办法]
--或者用下面的
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
[解决办法]
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 tbUser
left 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
[解决办法]
执行结果
小张 茶杯 3000
小张 玩具 400
合计 3400
小汪 茶杯 200
小汪 茶杯 400
小汪 玩具 1000
合计 1600
[解决办法]
--用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
*/
[解决办法]
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, '小张 ')
go
if 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)
go
if 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, '茶杯 ')
go
select 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 end
drop table [user],定购表,产品表
/*
username product price
---------- ---------- -----------
小汪 茶杯 600
小汪 玩具 1000
小汪 合计 1600
小张 茶杯 3000
小张 玩具 400
小张 合计 3400
合计 合计 5000
（所影响的行数为 7 行）
*/
