22,206
社区成员
发帖
与我相关
我的任务
分享
---测试数据
;WITH payment(id,customer_id,amout,other)AS(
select 1,1,10,'测试1' union all
select 2,2,20,'测试2' union all
select 3,2,30,'测试3' union all
select 4,3,40,'测试4' union all
select 5,3,50,'测试5' union all
select 6,4,60,'测试6' union all
select 7,5,70,'测试7' union all
select 8,5,80,'测试8' union all
select 9,6,90,'测试8' union all
select 10,7,100,'测试10'
),Customer(customer_id, first_name)AS(
select 1,'Ja' union all
select 2,'Jb' union all
select 3,'Jc' union all
select 4,'Jd' union all
select 5,'Je' union all
select 6,'Jf' union all
select 7,'Ga' union all
select 8,'Qa'
)
---测试数据结束,读取语句如下:
SELECT TOP 5
b.first_name ,
( SELECT SUM(a.amout)
FROM payment a
WHERE a.customer_id = b.customer_id
) AS amount
FROM Customer b
WHERE SUBSTRING(b.first_name, 1, 1) = 'J'
ORDER BY amount DESC;
select top(5) b.first_name,sum(a.amount) as amount
from payment a
inner join Customer b on b.customer_id=a.customer_id
where b.first_name like 'J%'
group by a.customer_id,b.first_name
order by sum(a.amount) desc
---测试数据
;WITH payment(id,customer_id,amout,other)AS(
select 1,1,10,'测试1' union all
select 2,2,20,'测试2' union all
select 3,2,30,'测试3' union all
select 4,3,40,'测试4' union all
select 5,3,50,'测试5' union all
select 6,4,60,'测试6' union all
select 7,5,70,'测试7' union all
select 8,5,80,'测试8' union all
select 9,6,90,'测试8' union all
select 10,7,100,'测试10'
),Customer(customer_id, first_name)AS(
select 1,'Ja' union all
select 2,'Jb' union all
select 3,'Jc' union all
select 4,'Jd' union all
select 5,'Je' union all
select 6,'Jf' union all
select 7,'Ga' union all
select 8,'Qa'
)
---测试数据结束,读取语句如下:
SELECT TOP 5
b.first_name ,
( SELECT SUM(a.amout)
FROM payment a
WHERE a.customer_id = b.customer_id
) AS amount
FROM Customer b
WHERE SUBSTRING(b.first_name, 1, 1) = 'J'
ORDER BY b.customer_id;
select 4,3,40,'测试4' union all
select 5,3,50,'测试5' union all
select 6,4,60,'测试6' union all
select 7,5,70,'测试7' union all
select 8,5,80,'测试8' union all
select 9,6,90,'测试8' union all
select 10,7,100,'测试10'
),Customer(customer_id, first_name)AS(
select 1,'Ja' union all
select 2,'Jb' union all
select 3,'Jc' union all
select 4,'Jd' union all
select 5,'Je' union all
select 6,'Jf' union all
select 7,'Ga' union all
select 8,'Qa'
)
---测试数据结束,读取语句如下:
SELECT TOP 5
b.first_name ,
( SELECT SUM(a.amout)
FROM payment a
WHERE a.customer_id = b.customer_id
) AS amount
FROM Customer b
WHERE SUBSTRING(b.first_name, 1, 1) = 'J'
ORDER BY b.customer_id;