求最优算法...可能是大家经常碰到的问题
有三个表,如LoginName存着用户的名字,GodsData存着货物的基本资料,SaleData存着销售具体数据,(表结构在后面)现在要算出一张表:以用户
为列,以货物资料为行,通过行与列的关系列出报表,
=====================================
| \ | 牙膏 | 牙刷 | 牙杯 |
=====================================
| 张三 | 11 | 23 | 0 |
| 李四 | 0 | 22 | 0 |
| 王五 | 0 | 0 | 34 |
=====================================
我现在的算法是这样子的,难道只能这样子了吗?求最优算法!!!
<table>
<tr>
<td> </td>
<% '行输出货名品名
sqlstr= "select GodsName from GodsData order by GodsId "
set rs=conn.execute(sqlstr)
whilt not rs.eof
Response.Write " <td> " & rs( "GodsName ") & " </td> "
rs.movenext
wend
rs.close
set rs=nothing
%>
</tr>
<% '第一个循环,输出第一列,即用户名字
sqlstr= "select * from GodsData order by GodsId "
set rs=conn.execute(sqlstr)
whilt not rs.eof
Response.Write " <tr> <td> " & rs( "GodsName ") & " </td> "
'第二个循环,类似表头读法,给出货物ID
sqlstr2= "select GodsId from GodsData order by GodsId "
set rs2=conn.execute(sqlstr2)
whilt not rs2.eof
'输出中间具体数据
sqlstr3= "select SaleQuantity from SaleData where UserId= " & rs( "UserId ") & " and GodsId= " & rs2( "GodsId ")
set rs3=conn.execute(sqlstr3)
If Not rs3.eof Then
Response.Write " <td> " & rs( "SaleQuantity ") & " </td> "
End If
rs2.movenext
wend
Response.write " </tr> "
rs.movenext
wend
rs.close
set rs=nothing
%>
</table>
***********************表结构***************************
(LoginName)
---------------------------
UserId|UserName
---------------------------
1|张三
2|李四
9|王五
--------------------------
(GodsData)
---------------------------
GodsId|GodsName
---------------------------
22|牙膏
65|牙刷
77|牙杯
--------------------------
(SaleData)
-------------------------
SaleId|UserId|GodsIs|SaleQuantity
--------------------------
1|1|22|11
2|2|65|22
3|9|77|34
4|1|65|23
--------------------------
------解决方案--------------------
declare @sql varchar(8000)
set @sql= 'select distinct userid '
select @sql=@sql+ ',sum(case when goodsis= '+cast(a.goodsid as varchar)+ ' then salequantity else 0 end) as '+a.goodsname
from goodsdata a
where a.goodsid in (select distinct b.goodsis from saledata b)
set @sql=@sql+ ' from saledata group by userid '
--print @sql
execute(@sql)
[解决办法]
用存储过程吧。。。
查询本来就是数据库的事，也是其优势。
[解决办法]
卖的产品不多才能用这样的表。动态SQL语句搞定。
