最近在维护一个项目,要新加一个功能,根据用户session里的ID,查得该用户的所有订购明细订单表结果如下
id signup_number products
1 2009001213 2,3,5,7
2 2009001213 2,3,5
3 2009001213 2
4 2009001557 4,5,6
5 2009001557 4,5
----------------------------------------
此表不是我设计的,询问后得知,
同一张单子 比如2009001213, 三条记录,代表2号商品订了3个,3号商品定了2个,5号2个,7号一个
同理,1557的单子 4号商品订了2个 5号2个 6号2个
对应的编号在产品表里可以查到商品的名字价格等. 求达人帮我想一个好办法把数据在网页里显示出来.最好有sql语句,和逻辑.
id signup_number products
1 2009001213 2,3,5,7
2 2009001213 2,3,5
3 2009001213 2
4 2009001557 4,5,6
5 2009001557 4,5
----------------------------------------
此表不是我设计的,询问后得知,
同一张单子 比如2009001213, 三条记录,代表2号商品订了3个,3号商品定了2个,5号2个,7号一个
同理,1557的单子 4号商品订了2个 5号2个 6号2个
对应的编号在产品表里可以查到商品的名字价格等. 求达人帮我想一个好办法把数据在网页里显示出来.最好有sql语句,和逻辑.
select signup_number ,count(2), count(3), count(4),count(5)....
from tab
group(signup_number)
select top 1000 id=identity(int,1,1) into 序数表 from syscolumns a,syscolumns b
go
--测试数据
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[signup_number] int,[products] varchar(7))
insert [TB]
select 1,2009001213,'2,3,5,7' union all
select 2,2009001213,'2,3,5' union all
select 3,2009001213,'2' union all
select 4,2009001557,'4,5,6' union all
select 5,2009001557,'4,5'--sql语句
select keyword=substring(a.products,b.id,charindex(',',a.products+',',b.id)-b.id)+'号产品',
[count]=count(distinct a.id)
from TB a,序数表 b
where b.id<=len(a.products)
and substring(','+a.products,b.id,1)=',' --要是统计signup_number自己加条件
group by substring(a.products,b.id,charindex(',',a.products+',',b.id)-b.id)+'号产品'/*
keyword count
------------- -----------
2号产品 3
3号产品 2
4号产品 2
5号产品 4
6号产品 1
7号产品 1(所影响的行数为 6 行)*/drop table TB,序数表
--> 测试数据: #tb
if object_id('tb') is not null drop table tb
go
create table tb (id int,signup_number varchar(18),products varchar(14) )
insert into tb
select 1,'2009001213','2,3,5,7' union all
select 2,'2009001213','2,3,5' union all
select 3,'2009001213','2' union all
select 4,'2009001557','4,5,6' union all
select 5,'2009001557','4,5'
select top 8000 id=identity(int,1,1) into # from syscolumns select name,数量=count(*) from(
select name=substring(a.products+',',b.id,charindex(',',a.products+',',b.id+1)-b.id)
from tb a inner join # b on substring(','+a.products,b.id,1)=','
--where a.signup_number='2009001213' ---这里可以指定一个signup_number
)t
group by name name 数量
--------------- -----------
2 3
3 2
4 2
5 4
6 1
7 1(6 行受影响)drop table #
insert opo select
1 , '2009001213' ,'2,3,5,7' union all select
2 , '2009001213' ,'2,3,5' union all select
3 , '2009001213' ,'2' union all select
4 , '2009001557' ,'4,5,6' union all select
5 , '2009001557' ,'4,5'
go
select signup_number as 商品单号 ,products as 商品号,COUNT(*) as 商品个数
from (
Select
a.signup_number,products=substring(a.products ,b.number,charindex(',',a.products+',',b.number)-b.number)
from
opo a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.products)
where
substring(','+a.products,b.number,1)=',') z
group by signup_number,products
order by signup_number、
/*
商品单号 商品号 商品个数
-------------------- ------------ -----------
2009001213 2 3
2009001213 3 2
2009001213 5 2
2009001213 7 1
2009001557 4 2
2009001557 5 2
2009001557 6 1
*/
from (
Select
a.signup_number,products=substring(a.products ,b.number,charindex(',',a.products+',',b.number)-b.number)
from
opo a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.products)
where
substring(','+a.products,b.number,1)=',') z
group by products
order by products
/*
商品号 商品个数
------------ -----------
2 3
3 2
4 2
5 4
6 1
7 1*/