现有两个表AAA和表BBB
表AAA (title可能是重复的这是商家发布的)
ID TITLE USER(用户)
1 t1 U1
2 t2 U2
3 t3 U5
4 t1 U7
5 t8 U12
6 t2 U12
......
表BBB (是user的用户销售title产品的TJ销售数量)
ID TITLE TJ(销售数量) USER(用户)
1 t1 2 u1
2 t1 1 u1
3 t1 3 U2
4 t2 5 U2
5 t5 1 U3
6 t3 2 u1
7 t1 6 u12
......
想要的效果是查询表AAA的title和user"按照"对应表BBB中title和user的TJ总数来排序(表BBB中不存在的也作为条件,一个是升序,一个降序)
这样还有个问题,下面能把对应的值输出来吗?
例如
<%=rs("id")%> <%=rs("title")%> <%=总的销售数量%>(好像有点难啊)
小弟只会ASP而且比较菜,遇到这么个头大的问题,搞了大半天了也搞不出来,求高手帮帮忙呀?
------解决方案--------------------
- SQL code
select a.* from AAA aleft join (select [USER], TITLE, sum(TJ)TJ from BBB group by [USER], TITLE) bon a.[USER]=b.[USER] and a.TITLE=b.TITLEorder by b.TJ
------解决方案--------------------
- SQL code
declare @表AAA table (ID int,TITLE varchar(2),USERName varchar(3))insert into @表AAAselect 1,'t1','U1' union allselect 2,'t2','U2' union allselect 3,'t3','U5' union allselect 4,'t1','U7' union allselect 5,'t8','U12' union allselect 6,'t2','U12'declare @表BBB table (ID int,TITLE varchar(2),TJ int,USERName varchar(3))insert into @表BBBselect 1,'t1',2,'u1' union allselect 2,'t1',1,'u1' union allselect 3,'t1',3,'U2' union allselect 4,'t2',5,'U2' union allselect 5,'t5',1,'U3' union allselect 6,'t3',2,'u1' union allselect 7,'t1',6,'u12'--不明白要谁升序，谁降序select *,(select sum(TJ) from @表BBB where title=a.title and username=a.username) as B表中的TJ和from @表AAA a order by 4 /*ID TITLE USERName B表中的TJ和----------- ----- -------- -----------3 t3 U5 NULL4 t1 U7 NULL5 t8 U12 NULL6 t2 U12 NULL1 t1 U1 32 t2 U2 5*/
------解决方案--------------------
- SQL code
create table A(ID int,TITLE varchar(10),[USER] varchar(10))insert Aselect 1,'t1','U1' union allselect 2,'t2','U2' union allselect 3,'t3','U12' union allselect 4,'t5','U7' union allselect 5,'t1','U2' union allselect 6,'t2','U12'create table B(ID int,TITLE varchar(10),TJ int,[USER] varchar(10))insert Bselect 1, 't1', 2, 'u1' union allselect 2, 't1', 1, 'u1' union allselect 3, 't1', 3, 'U2' union allselect 4, 't2', 5, 'U2' union allselect 5, 't5', 1, 'U7' union allselect 6, 't2', 6, 'u12' union allselect 8, 't2', 4, 'u2'goselect A.ID,A.Title,A.[User],TJ=sum(isnull(b.TJ,0)) from Aleft join B on B.[user]=A.[user] and B.Title=A.Title group by A.ID,A.Title,A.[User]order by sum(isnull(b.TJ,0)) desc/*ID Title User TJ---- ---- ---- --2 t2 U2 96 t2 U12 65 t1 U2 31 t1 U1 34 t5 U7 13 t3 U12 0*/godrop table A,B