테이블은 일단 두개로
이렇게 두개로 구성 되어 있습니다.
첫번째 문제는
하루에 2회이상(2회 포함) 주문한 고객의 id와 주문 물품을 구하라
이건데요!
select cid,item
from orders
group by cid,odate
having count(*) >= 2;
이렇게 접근 했더니 안되더라구요...하..
두번째 문제는
2명 이상의 고객이 거주하는 state에 대하여, state이름과 state별 고객 수, state별 total 주문횟수를 보여라.
이건데
select t1.state,count(t1.state) as '총고객수',count(t2.cid) as '총주문회수'
from (select * from customers group by state having count(customers.cid)>=2) as t1 join (select * from customers join orders on customers.cid=orders.cid) as t2
on t1.state=t2.state
group by t1.state ;
이렇게 접근 했더니 총고객수랑 총주문횟수 값이 동일하게 나온답니다..
고수님들 도와주세요!!
Comment 5
-
흑흑
2015.12.17 00:24
-
minsouk
2015.12.17 00:43
use tempdb
goif object_id('customers') is not null
drop table customers
gocreate table customers
(cid int
,fname varchar(100)
,lname varchar(100)
,city varchar (100)
,state varchar(100)
)
go
if object_id('orders') is not null
drop table orders
gocreate table orders
(cid int
,odate datetime
,item varchar(100)
,quantity int
,price int
)
go
insert customers values (1, 'j1', 'j1last', 'se1', 'wa1')
insert customers values (2, 'j2', 'j2last', 'se2', 'wa2')
insert customers values (3, 'j3', 'j3last', 'se3', 'wa2')
insert customers values (4, 'j3', 'j3last', 'se3', 'wa3')
insert customers values (5, 'j3', 'j3last', 'se3', 'wa3')
insert customers values (6, 'j3', 'j3last', 'se3', 'wa3')go
insert orders values (1, '20110610', 'it1', 1, 28)
insert orders values (2, '20110620', 'it2', 1, 58)
insert orders values (2, '20110620', 'it3', 1, 33)
insert orders values (3, '20110630', 'it3', 1, 33)
insert orders values (3, '20110630', 'it3', 1, 33)
insert orders values (3, '20110630', 'it3', 1, 33)
insert orders values (4, '20110610', 'it3', 1, 33)go
select * from customers
select * from ordersgo
--sol1)
select b.cid, b.item
from
(
select cid
from orders
group by cid, odate
having count(*) >= 2
) a
join orders b
on a.cid = b.cid--sol2-1)
select state, max(cnt_resident) cnt_residents, count(state) cnt_orders
from
(
select a.state, b.cid, a.cnt_resident
from
(
select state, count(*) cnt_resident
from customers
group by state
having count(*) >= 2
) a
join customers b
on a.state = b.state
) a
join orders b
on a.cid = b.cid
group by state--sol2-2)
select a.state, max(a.cnt_residents) cnt_residents, sum(cnt_orders) cnt_orders
from
(
select a.state, a.cnt_residents, (select count(*) from orders where cid = b.cid) cnt_orders
from
(
select state, count(*) cnt_residents
from customers
group by state
having count(*) >= 2
) a
join customers b
on a.state = b.state
) a
group by a.state -
희망나라
2015.12.17 00:58
create table orders (
cid varchar(10) null
,cdate varchar(10) null
,item varchar(10) null
)
insert into orders values ('10101','2015-12-01', 'A')
,('10101','2015-12-01', 'B')
,('10102','2015-12-01', 'A')
,('10103','2015-12-02', 'C')
,('10103','2015-12-02', 'D')
,('10104','2015-12-03', 'A')
select o.cid, o.cdate, (SELECT COLB = (SELECT item + ','
FROM orders AS A
WHERE o.cid = A.cid
FOR XML PATH ('' ))) from orders o
group by o.cid, o.cdate having COUNT(*) >= 2 -
처리짱
2015.12.17 10:42
탈모방지를 위해 많은 분들이 도움을 주시네요;;
쿼리가 길어질경우는 굳이 한 쿼리 안에서 모든걸 해결하려 하실 필요는 없습니다.
예를들면,, 2명 이상의 고객이 거주하는 state에 대하여, state이름과 state별 고객 수, state별 total 주문횟수를 보여라.
2명이 이상의 거주고객의 state -> select state, count(*) into #TEMP from customers group by state having count(*) > 2
#TEMP등과 같은 테이블로 뽑아놓고
해당테이블로 다음 조건에 맞게 조인을 걸어 나가시면 되겟죠..
-
Terry
2015.12.17 10:57
2번..
---쿼리시작---
;with customers (cid,fname,lname,city,state) As
(
Select 1, 'j1', 'j1last', 'se1', 'wa1' Union All
Select 2, 'j2', 'j2last', 'se2', 'wa2' Union All
Select 3, 'j3', 'j3last', 'se3', 'wa2' Union All
Select 4, 'j3', 'j3last', 'se3', 'wa3' Union All
Select 5, 'j3', 'j3last', 'se3', 'wa3' Union All
Select 6, 'j3', 'j3last', 'se3', 'wa3'
)
,orders ( cid,cdate,item) As
(
Select 1,'2015-12-01', 'A' Union All
Select 2,'2015-12-01', 'B' Union All
Select 3,'2015-12-01', 'A' Union All
Select 4,'2015-12-02', 'C' Union All
Select 5,'2015-12-02', 'D' Union All
Select 6,'2015-12-03', 'A'
)
Select
a.state
,a.cnt As cid_cnt
,b.cnt As order_cnt
From (
Select a.state
,count(1) As cnt
From customers a
Group By a.state
Having Count(a.cid) >= 2
) a Inner Join
(
Select Count(1) As cnt
,b.state
From orders a Inner Join customers b On a.cid = b.cid
Group By b.state
) b
On a.state = b.state---쿼리끝---
select cid, item, count(*)
from orders
group by cid, item
having count(*) > 1