现在手头有两个表,一个是表product,另一个是表saleinfo,两个表的关联字段就是itemnumber,表的结构如下:product表:(某款商品的基本描述)(产品款号) (产品名称) (产品描述)
itemnumber productname description item1 name1 desc1 item2 name2 desc2 item3 name3 desc3......
......
saleinfo表的:(同一款产品,销售给不同的客户,价格可能不同,而且同一款号最多重复二次)(产品款号) (产品价格) (产品销售的客户)
itemnumber itemprice itemclient item1 item1price1 item1client1 (第一款的第一个客户及价格) item1 item1price2 item1client2 (第一款的第二个客户及价格) item2 item1price1 item1client1 (第二款的第一个客户机价格) item3 item3price1 item3client1 (第三款的第一个客户及价格) item3 item3price2 item3client2 (第三款的第二个客户及价格)...
...
现在,我想生成一个新的表,就叫做purpose吧,内容就是这2个表的集合,格式如下:purpose表:(产品款号) (产品名称) (产品描述)(第一个客户价格)(第一个客户)(第二个客户价格)(第二个客户)
itemnumber productname description itemprice1 itemclient1 itemprice2 itemclient2 item1 name1 desc1 item1price1 item1client1 item1price2 item1client2 item2 name2 desc2 item1price2 item1client2 item3 name3 desc3 item3price1 item3client1 item3price2 item3client2..........
请问,SQL语句如何写,才能通过前面的2个表,生成我想要的第三个表。还有,大家的思路是什么?现在感觉没有思路了。
itemnumber productname description item1 name1 desc1 item2 name2 desc2 item3 name3 desc3......
......
saleinfo表的:(同一款产品,销售给不同的客户,价格可能不同,而且同一款号最多重复二次)(产品款号) (产品价格) (产品销售的客户)
itemnumber itemprice itemclient item1 item1price1 item1client1 (第一款的第一个客户及价格) item1 item1price2 item1client2 (第一款的第二个客户及价格) item2 item1price1 item1client1 (第二款的第一个客户机价格) item3 item3price1 item3client1 (第三款的第一个客户及价格) item3 item3price2 item3client2 (第三款的第二个客户及价格)...
...
现在,我想生成一个新的表,就叫做purpose吧,内容就是这2个表的集合,格式如下:purpose表:(产品款号) (产品名称) (产品描述)(第一个客户价格)(第一个客户)(第二个客户价格)(第二个客户)
itemnumber productname description itemprice1 itemclient1 itemprice2 itemclient2 item1 name1 desc1 item1price1 item1client1 item1price2 item1client2 item2 name2 desc2 item1price2 item1client2 item3 name3 desc3 item3price1 item3client1 item3price2 item3client2..........
请问,SQL语句如何写,才能通过前面的2个表,生成我想要的第三个表。还有,大家的思路是什么?现在感觉没有思路了。
解决方案 »
- 求解:获得本机当前的网络数据流量?
- 刚学delphi二天,求delphi6.0下载地址(*能正确安装,不要有错误的哪种-中文版最好)-delphi控件使用大用
- 将memo内容导出到EXCEL时如果自动识别换行???
- 有一数组传送,
- 速达3000导航栏的一个问题
- 请各位大虾帮帮忙!
- DBExpress打开MSSQL200存储过程的问题
- 我有一批word文件,文件名都不规则,如何编一个程序用打开DOC文件中的标题来对文件名进行重命名?
- Delphi启动Excel或PowerPoint
- 给ListView网格着色。用OnCustomDrawItem事件,无法达到预期效果
- 关于大对象取图片
- 有多少人是自己给自己打工的?就是自己接业务干
select * , KHJG1 = (SELECT itemprice
FROM (SELECT RowId = ROW_NUMBER() OVER (ORDER BY itemnumber), *
FROM saleinfo
WHERE saleinfo.itemnumber = T_CSJ_TRTZ.itemnumber) AS mytable
WHERE ZYL_RowId = 1),KH1 = (SELECT itemclient
FROM (SELECT RowId = ROW_NUMBER() OVER (ORDER BY itemnumber), *
FROM saleinfo
WHERE saleinfo.itemnumber = T_CSJ_TRTZ.itemnumber) AS mytable
WHERE ZYL_RowId = 1),KHJG2 = (SELECT itemprice
FROM (SELECT RowId = ROW_NUMBER() OVER (ORDER BY itemnumber), *
FROM saleinfo
WHERE saleinfo.itemnumber = T_CSJ_TRTZ.itemnumber) AS mytable
WHERE ZYL_RowId = 2),KH2 = (SELECT itemclient
FROM (SELECT RowId = ROW_NUMBER() OVER (ORDER BY itemnumber), *
FROM saleinfo
WHERE saleinfo.itemnumber = T_CSJ_TRTZ.itemnumber) AS mytable
WHERE ZYL_RowId = 2) ----如果有第N个,就继续循环,如果数量有限制就很容易,只要达到最大限制就可以 from product
(产品款号) (产品名称) (产品描述)(客户价格)(客户)
返回的结果集操作
一定要在sql语句中?
select * , KHJG1 = (SELECT itemprice
FROM (SELECT RowId = ROW_NUMBER() OVER (ORDER BY itemnumber), *
FROM saleinfo
WHERE saleinfo.itemnumber = product.itemnumber) AS mytable
WHERE RowId = 1),KH1 = (SELECT itemclient
FROM (SELECT RowId = ROW_NUMBER() OVER (ORDER BY itemnumber), *
FROM saleinfo
WHERE saleinfo.itemnumber = product.itemnumber) AS mytable
WHERE RowId = 1),KHJG2 = (SELECT itemprice
FROM (SELECT RowId = ROW_NUMBER() OVER (ORDER BY itemnumber), *
FROM saleinfo
WHERE saleinfo.itemnumber = product.itemnumber) AS mytable
WHERE RowId = 2),KH2 = (SELECT itemclient
FROM (SELECT RowId = ROW_NUMBER() OVER (ORDER BY itemnumber), *
FROM saleinfo
WHERE saleinfo.itemnumber = product.itemnumber) AS mytable
WHERE RowId = 2) ----如果有第N个,就继续循环,如果数量有限制就很容易,只要达到最大限制就可以 from product
刚才表名写错了
unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB;type
TForm1 = class(TForm)
Button1: TButton;
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
ADOQuery2: TADOQuery;
ADOConnection2: TADOConnection;
ADOConnection3: TADOConnection;
ADOQuery3: TADOQuery;
Edit1: TEdit;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
var
i,j,k,tsum :integer; //循环数值
sqltext:string;
sql:array[1..7] of string;
s0,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13,s14,s15:string;
begin
tsum:=0; //'update mubiao set itemspeed1='''+adoquery2.fieldvalues['itemspeed']+''',itemsort1='''+adoquery2.fieldvalues['itemsort']+''' where item='''+adoquery1.fieldvalues['item']+'''' adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('select * from mubiao');
adoquery1.Open;
showmessage(inttostr(adoquery1.RecordCount));
for i:=1 to adoquery1.RecordCount do
begin adoquery2.Close;
adoquery2.SQL.Clear;
adoquery2.SQL.Add('select * from rosette where item='''+adoquery1.fieldvalues['item']+'''');
adoquery2.Open;
//showmessage(inttostr(adoquery2.RecordCount)); for j:=1 to adoquery2.RecordCount do
begin
adoquery3.Close;
adoquery3.SQL.Clear; if j=1 then adoquery3.SQL.Add('update mubiao set itemspeed1='''+adoquery2.fieldvalues['itemspeed']+''',itemsort1='''+adoquery2.fieldvalues['itemsort']+''' where item='''+adoquery1.fieldvalues['item']+'''');
if j=2 then adoquery3.SQL.Add('update mubiao set itemspeed2='''+adoquery2.fieldvalues['itemspeed']+''',itemsort2='''+adoquery2.fieldvalues['itemsort']+''' where item='''+adoquery1.fieldvalues['item']+'''');
if j=3 then adoquery3.SQL.Add('update mubiao set itemspeed3='''+adoquery2.fieldvalues['itemspeed']+''',itemsort3='''+adoquery2.fieldvalues['itemsort']+''' where item='''+adoquery1.fieldvalues['item']+'''');
if j=4 then adoquery3.SQL.Add('update mubiao set itemspeed4='''+adoquery2.fieldvalues['itemspeed']+''',itemsort4='''+adoquery2.fieldvalues['itemsort']+''' where item='''+adoquery1.fieldvalues['item']+'''');
if j=5 then adoquery3.SQL.Add('update mubiao set itemspeed5='''+adoquery2.fieldvalues['itemspeed']+''',itemsort5='''+adoquery2.fieldvalues['itemsort']+''' where item='''+adoquery1.fieldvalues['item']+'''');
if j=6 then adoquery3.SQL.Add('update mubiao set itemspeed6='''+adoquery2.fieldvalues['itemspeed']+''',itemsort6='''+adoquery2.fieldvalues['itemsort']+''' where item='''+adoquery1.fieldvalues['item']+'''');
if j=7 then adoquery3.SQL.Add('update mubiao set itemspeed7='''+adoquery2.fieldvalues['itemspeed']+''',itemsort7='''+adoquery2.fieldvalues['itemsort']+''' where item='''+adoquery1.fieldvalues['item']+''''); adoquery3.SQL.Add(sql[j]);
//showmessage(adoquery3.SQL.Text);
adoquery3.ExecSQL; tsum:=tsum+1;
adoquery2.Next;
end;
adoquery1.Next;
end; showmessage('共循环了'+inttostr(tsum)+'次');
end;end.