s***c
发帖数: 1664 | 1 比如一个表
cust date prod
1 1/1/2015 A
1 1/2/2015 B
1 1/3/2015 B
2 1/3/2015 C
2 1/4/2015 D
怎么变成下面这个表，不管customer什么时候买过某个产品，买过几次，只要买过，就
记成1，否则0
cust A B C D
1 1 1 0 0
2 0 0 1 1
谢谢~ | S******y
发帖数: 1123 | 2 you might want to use CASE statement
CASE is used to provide if-then-else type of logic to SQL
---
欢迎浏览Python/R/Hadoop实战速成课网页-
http://plus.google.com/+statsGuyMITBBS/about | t*****w
发帖数: 254 | 3 create table #cust(cust int, date datetime, prod char(1))
insert into #cust(cust , date , prod )
values( '1' , '1/1/2015' , 'A'), ('1' , '1/2/2015' , 'B'),
('1' , '1/3/2015' , 'B'),('2' , '1/3/2015' , 'C'),('2' , '1/4/
2015' , 'D')
drop table #cust2
select distinct cust, prod ,
A=case when prod ='A' then 1 else 0 end ,
B=case when prod ='B' then 1 else 0 end ,
C=case when prod ='C' then 1 else 0 end ,
D=case when prod ='D' then 1 else 0 end
into #cust2 from #cust
select cust, (cast(MAX(A) as CHAR(1))+ cast(MAX(B) as CHAR(1))+cast(MAX(C)
as CHAR(1))+cast(MAX(D) as CHAR(1)))ABCD
from #cust2 group by cust
【在 s***c 的大作中提到】
: 比如一个表
: cust date prod
: 1 1/1/2015 A
: 1 1/2/2015 B
: 1 1/3/2015 B
: 2 1/3/2015 C
: 2 1/4/2015 D
: 怎么变成下面这个表，不管customer什么时候买过某个产品，买过几次，只要买过，就
: 记成1，否则0
: cust A B C D
| s*****d
发帖数: 2667 | 4 MySql:
create table cust2 as
(select cust,
IF(prod='A',1,0) as A,
IF(prod='B',1,0) as B,
IF(prod ='C',1,0) as C,
IF(prod='D',1,0) as D
from cust);
select cust,max(A) as A,max(B) as B,max(C) as C,max(D) as D
from cust2
group by cust;
【在 s***c 的大作中提到】
: 比如一个表
: cust date prod
: 1 1/1/2015 A
: 1 1/2/2015 B
: 1 1/3/2015 B
: 2 1/3/2015 C
: 2 1/4/2015 D
: 怎么变成下面这个表，不管customer什么时候买过某个产品，买过几次，只要买过，就
: 记成1，否则0
: cust A B C D
| s***c
发帖数: 1664 | 5 谢了，我后来也这么写的，好在产品少。要是有一万个产品，这个code得多长啊
【在 s*****d 的大作中提到】
: MySql:
: create table cust2 as
: (select cust,
: IF(prod='A',1,0) as A,
: IF(prod='B',1,0) as B,
: IF(prod ='C',1,0) as C,
: IF(prod='D',1,0) as D
: from cust);
: select cust,max(A) as A,max(B) as B,max(C) as C,max(D) as D
: from cust2
| s***c
发帖数: 1664 | 6 比如一个表
cust date prod
1 1/1/2015 A
1 1/2/2015 B
1 1/3/2015 B
2 1/3/2015 C
2 1/4/2015 D
怎么变成下面这个表，不管customer什么时候买过某个产品，买过几次，只要买过，就
记成1，否则0
cust A B C D
1 1 1 0 0
2 0 0 1 1
谢谢~ | S******y
发帖数: 1123 | 7 you might want to use CASE statement
CASE is used to provide if-then-else type of logic to SQL
---
欢迎浏览Python/R/Hadoop实战速成课网页-
http://plus.google.com/+statsGuyMITBBS/about | t*****w
发帖数: 254 | 8 create table #cust(cust int, date datetime, prod char(1))
insert into #cust(cust , date , prod )
values( '1' , '1/1/2015' , 'A'), ('1' , '1/2/2015' , 'B'),
('1' , '1/3/2015' , 'B'),('2' , '1/3/2015' , 'C'),('2' , '1/4/
2015' , 'D')
drop table #cust2
select distinct cust, prod ,
A=case when prod ='A' then 1 else 0 end ,
B=case when prod ='B' then 1 else 0 end ,
C=case when prod ='C' then 1 else 0 end ,
D=case when prod ='D' then 1 else 0 end
into #cust2 from #cust
select cust, (cast(MAX(A) as CHAR(1))+ cast(MAX(B) as CHAR(1))+cast(MAX(C)
as CHAR(1))+cast(MAX(D) as CHAR(1)))ABCD
from #cust2 group by cust
【在 s***c 的大作中提到】
: 比如一个表
: cust date prod
: 1 1/1/2015 A
: 1 1/2/2015 B
: 1 1/3/2015 B
: 2 1/3/2015 C
: 2 1/4/2015 D
: 怎么变成下面这个表，不管customer什么时候买过某个产品，买过几次，只要买过，就
: 记成1，否则0
: cust A B C D
| s*****d
发帖数: 2667 | 9 MySql:
create table cust2 as
(select cust,
IF(prod='A',1,0) as A,
IF(prod='B',1,0) as B,
IF(prod ='C',1,0) as C,
IF(prod='D',1,0) as D
from cust);
select cust,max(A) as A,max(B) as B,max(C) as C,max(D) as D
from cust2
group by cust;
【在 s***c 的大作中提到】
: 比如一个表
: cust date prod
: 1 1/1/2015 A
: 1 1/2/2015 B
: 1 1/3/2015 B
: 2 1/3/2015 C
: 2 1/4/2015 D
: 怎么变成下面这个表，不管customer什么时候买过某个产品，买过几次，只要买过，就
: 记成1，否则0
: cust A B C D
| s***c
发帖数: 1664 | 10 谢了，我后来也这么写的，好在产品少。要是有一万个产品，这个code得多长啊
【在 s*****d 的大作中提到】
: MySql:
: create table cust2 as
: (select cust,
: IF(prod='A',1,0) as A,
: IF(prod='B',1,0) as B,
: IF(prod ='C',1,0) as C,
: IF(prod='D',1,0) as D
: from cust);
: select cust,max(A) as A,max(B) as B,max(C) as C,max(D) as D
: from cust2
| g**********y
发帖数: 423 | 11 如果有一万个的话，可以这么写：
SELECT CONCAT(
'CREATE TABLE CUST3 AS',
'(SELECT CUST,',
GROUP_CONCAT(REPLACE(' IF(PROD="PROD_NAME",1,0) AS PROD_NAME', 'PROD_NAME',
T.PROD) SEPARATOR ','),
' FROM CUST);'
)
INTO @CREATE_SQL FROM (SELECT DISTINCT PROD FROM CUST) AS T;
PREPARE STMT FROM @CREATE_SQL;
EXECUTE STMT;
SELECT CONCAT(
'SELECT CUST,',
GROUP_CONCAT(REPLACE(' MAX(PROD_NAME) AS PROD_NAME', 'PROD_NAME', T.PROD)
SEPARATOR ','),
' FROM CUST3',
' GROUP BY CUST;'
)
INTO @SELECT_SQL FROM (SELECT DISTINCT PROD FROM CUST) AS T;
PREPARE STMT FROM @SELECT_SQL;
EXECUTE STMT; | s***c
发帖数: 1664 | |
|
