你要求的是交叉查询,我只知道在ACCESS里可以实现,在其它数据引擎里都不支持。你试试吧。如果你用的是ACCESS在建查询的时候选择查询类型为交叉查询就行。
解决方案 »
- VB 编程 问题
- vb 如何读取网页缓存中的内容 Temporary Internet Files
- 显示错误:要求对象!怎么办那?
- VB6+Crystal Report10的打包問題.
- VB编程如何实现热键?
- shellexecute打开一个TXT文件,看不到NOTEPAD窗口,可是查看进程却看到NOTEPAD(急。。)
- vsprinter上面翻页按钮可以自定义吗?
- 寻求用vb编写动态报表生成器的例子或者方法---给50分
- 关于数据绑定控件的问题。
- 用ADO如何执行一个储存过程,如何使用INPUT、OUTPUT参数
- 关于SQL的问题。
- 如何动态设定Crystal Report 的表头内容(4.5的版本)
但由于不是完全的Sum操作,好象交叉查询也未必行。一句话是不可以的,但
可以封在一个存储过程里边。建立一个放结果的表Temp_Result
字段如下Item_No | Vendor_No1 | Price1 | Vendor_No2 | Price2 | Vendor_No3 | Price3代码如下truncate table [Temp_Result]
declare @i int
declare @Item_No varchar(10)
declare @Item_Old varchar(10)
declare @Vendor_no varchar(30)
declare @Price int
declare @Ssql varchar(1000)
set @i = 0
declare cur_Temp cursor scroll for
select Item_No, Vendor_no, Price from Temp order by Item_No, Vendor_No
open cur_temp
Fetch First from cur_temp into
@Item_Old, @Vendor_no, @Price
Fetch First From cur_temp into
@Item_No, @Vendor_no, @Price
while @@fetch_status = 0
Begin
If (@Item_No = @Item_Old) and (@i <=3)
Begin
set @i = @i + 1
If Not Exists(Select * From Temp_Result where Item_No=@Item_No)
set @Ssql = 'Insert Into [Temp_Result](Item_no,Vendor_No1,Price1) Values (''' + @Item_No + ''',''' + @Vendor_No + ''',' + convert(varchar(30), @Price) + ')'
Else
set @Ssql = 'Update [Temp_Result] set Vendor_No' + convert(varchar(10),@i) + '= ''' + @Vendor_No + ''' , Price' + convert(varchar(10), @i) + '=' + convert(varchar(30), @Price) + ' where Item_no= ''' + @Item_Old + ''''
execute (@ssql)
Fetch Next from cur_temp into
@Item_No, @Vendor_no, @Price
End
Else
Begin
set @i = 0
set @Item_Old = @Item_No
End
End
close cur_Temp
deallocate cur_Tempselect * from Temp_Result
不符合范式的结果集只能通过这种方法取得。
我用了select語句跟遊標的做法.請各位指正:
下列語句在SQL server Query Analyzer 下執行通過.
declare @item_No_1 varchar(20)
declare @item_No_2 varchar(20)
declare @aa_vender varchar(20)
declare @bb_vender varchar(20)
declare @cc_vender varchar(20)
declare @aa_price numeric(18,2)
declare @bb_price numeric(18,2)
declare @cc_price numeric(18,2)
declare @i int
declare dd_cur scroll cursor
for
select aa.item_no ,(aa.vender_no), (aa.price), (bb.vender_no), (bb.price), (cc.vender_no), (cc.price )
from TBL_TEMP as aa left join TBL_TEMP as bb on aa.item_No=bb.item_no and aa.vender_No<>bb.vender_no
left join TBL_TEMP as cc on aa.item_no=cc.item_no and aa.vender_no<>cc.vender_no and bb.vender_no<>cc.vender_No open dd_cur
set @i=1
set @item_no_2=''
while @i<=@@cursor_rows
begin
if @i>@@cursor_rows
begin
break
end
if @i<=@@cursor_rows
begin
fetch absolute @i from dd_cur into @item_No_1,@aa_vender,@aa_price,@bb_vender,@bb_Price,@cc_vender,@cc_Price
if @item_No_1 !=@item_No_2
begin
set @item_No_2=@item_No_1
select @item_No_1,@aa_vender,@aa_price,@bb_vender,@bb_Price,@cc_vender,@cc_Price
end
set @i=@i+1
continue
end
end
close dd_cur
deallocate dd_cur
上述查詢結果可以把它寫進臨時表中供查詢.
Item_NO Vender_NO Price .
varchar(20) varchar(20) numeric(18,2)
同時物料最多不会超过 3 个供应商, 但有可能少于3个
即在Tbl_temp 表中有相同Item_NO的記錄最多只有三條.
如有多于三條的,可在上述語句中的遊標定義部分做相應改動,即自身多重JOIN
select t.item_no,t.vendor_no,t.Price,t1.vendor_no,t1.price,t2.vendor_no,t2.price
from t,t AS t1 ,t AS T2
WHERE T.item_no = T1.item_no and T1.item_no =T2.item_no
and T.vendor_no <>t1.vendor_no and t1.vendor_no <> t2.vendor_no
and T.vendor_no <>t2.vendor_no and t.vendor_no = (SELECT Min(vendor_no) FROM T AS T3 WHERE T3.item_no =T.item_no)
AND t2.vendor_no = (SELECT Max(vendor_no) FROM T AS T4 WHERE T1.item_no =T.item_no)
select t.item_no,t.vendor_no,t.Price,t1.vendor_no,t1.price,t2.vendor_no,t2.price
from t,t AS t1 ,t AS T2
WHERE T.item_no = T1.item_no and T1.item_no =T2.item_no
and T.vendor_no <t1.vendor_no and t1.vendor_no < t2.vendor_no
from TBL_TEMP as aa left join TBL_TEMP as bb on aa.item_No=bb.item_no and aa.vender_No<>bb.vender_no
left join TBL_TEMP as cc on aa.item_no=cc.item_no and aa.vender_no<>cc.vender_no and bb.vender_no<>cc.vender_No
where aa.vender_No>isnull(bb.vender_No,'') and aa.vender_No>isnull(cc.vender_No,'') and isnull(bb.vender_No,'a')>isnull(cc.vender_no,'')
order by aa.item_No快加分.呵呵!