有个用户购买商品的记录表record:
userid content
aaa 诺基亚5200
aaa nokia n73
aaa 诺基亚nokia
abc 摩托罗拉
aaa moto
abc 西门子
……另有一张品牌信息表brand,列出了各个品牌可能有的别名:
brandid brandname
1 诺基亚
1 nokia
2 摩托罗拉
2 moto
3 西门子
3 siemens
……我要得到每个用户对每个品牌的购买数result:
userid brandid num
aaa 1 3
aaa 2 1
abc 2 1
abc 3 1比如商品名称是“诺基亚nokia”,就认为是购买了brandid为1的商品1次
用户aaa购买的是诺基亚5200,nokia n73,诺基亚nokia,认为他购买了3个nokia产品。
怎么处理才能得到我要的结果呢?
userid content
aaa 诺基亚5200
aaa nokia n73
aaa 诺基亚nokia
abc 摩托罗拉
aaa moto
abc 西门子
……另有一张品牌信息表brand,列出了各个品牌可能有的别名:
brandid brandname
1 诺基亚
1 nokia
2 摩托罗拉
2 moto
3 西门子
3 siemens
……我要得到每个用户对每个品牌的购买数result:
userid brandid num
aaa 1 3
aaa 2 1
abc 2 1
abc 3 1比如商品名称是“诺基亚nokia”,就认为是购买了brandid为1的商品1次
用户aaa购买的是诺基亚5200,nokia n73,诺基亚nokia,认为他购买了3个nokia产品。
怎么处理才能得到我要的结果呢?
from Record a inner join brand b on a.content=b.brandname
group by a.userid,b.brandid
但是我不知道怎么处理
select a.userid,b.brandid,count(1) as num
from Record a inner join brand b on a.content like '%'+b.brandname +'%'
group by a.userid,b.brandid
insert into @tb1 select 'aaa','诺基亚5200'
insert into @tb1 select 'aaa','nokia n73'
insert into @tb1 select 'aaa','诺基亚nokia'
insert into @tb1 select 'abc','摩托罗拉'
insert into @tb1 select 'aaa','moto'
insert into @tb1 select 'abc','西门子'
declare @brand table(brandid int,brandname varchar(20))
insert into @brand select 1,'诺基亚'
insert into @brand select 1,'nokia'
insert into @brand select 2,'摩托罗拉'
insert into @brand select 2,'moto'
insert into @brand select 3,'西门子'
insert into @brand select 3,'siemens'
select a.userid,b.brandid,count(1) as num
from @tb1 a
inner join @brand b
on substring(a.content,1,len(b.brandname))=b.brandname
group by a.userid,b.brandid/*userid brandid num
------ ----------- -----------
aaa 1 3
aaa 2 1
abc 2 1
abc 3 1
*/
create table t01
(
userid varchar(20),
content varchar(20)
)
insert into t01
SELECT 'aaa','诺基亚5200' UNION ALL
SELECT 'aaa','nokia n73' UNION ALL
SELECT 'aaa','诺基亚nokia' UNION ALL
SELECT 'abc','摩托罗拉' UNION ALL
SELECT 'aaa','moto' UNION ALL
SELECT 'abc','西门子'create table t02
(
brandid int,
brandname varchar(20)
)insert into t02
SELECT 1,'诺基亚' UNION ALL
SELECT 1,'nokia' UNION ALL
SELECT 2,'摩托罗拉' UNION ALL
SELECT 2,'moto' UNION ALL
SELECT 3,'西门子' UNION ALL
SELECT 3,'siemens'select userid,brandid,[num] = count(1)
from
(
SELECT userid,content,brandid FROM t01 A INNER JOIN t02 B
ON CHARINDEX(B.brandname,content)>0
group by userid,content,brandid
) a GROUP BY userid, brandidDROP TABLE t01,t02 userid brandid num
-------------------- ----------- -----------
aaa 1 3
aaa 2 1
abc 2 1
abc 3 1
--精简了一下,其实可以用distinctSELECT userid,brandid,count(distinct content) FROM t01 A INNER JOIN t02 B
ON CHARINDEX(B.brandname,content)>0
group by userid,brandid
(但是a.content必須是以b.brandname開頭)select a.userid,b.brandid,count(1) as num
from Record a inner join brand b substring(a.content,1,len(b.brandname))=b.brandname
group by a.userid,b.brandid