不看会后悔的Windows XP之经验谈	简单快捷DIY实用家庭影院

关于组合查询结果（急！！！）

楼主sTommy（阿木）2001-06-06 17:42:00 在 Delphi / VCL组件开发及应用提问

现要统计客户、产品汇总数量的查询，要求为客户为基础，统计出各客户不同产品的数量。客户数目、产品类型都不确定，且所得查询结果的列应为：

字段：客户名称产品1数量产品2数量产品2数量产品2数量……
字段值： 01 100 105 80 130

敬请不吝赐教！！！问题点数：20、回复次数：16Top

1 楼hxshanji（洪兴山鸡）回复于 2001-06-06 17:47:00 得分 0

select sum(num1) as num1,sum(num2) as num2....... from yourtable group by khnameTop

2 楼sTommy（阿木）回复于 2001-06-06 17:59:00 得分 0

hxshanji兄：
我的产品发送数据表的字段只有：客户编号、产品编号、发送数量。
也就是说，产品数量只有一个字段，而查询条件取不同。重要的是怎么
把几个查询结果组合起来呢？请赐教。Top

3 楼sTommy（阿木）回复于 2001-06-06 18:29:00 得分 0

没人知道，还是分太少？？？
急急急！！！
没人知道，还是分太少？？？
急急急！！！
没人知道，还是分太少？？？
急急急！！！
没人知道，还是分太少？？？
急急急！！！
没人知道，还是分太少？？？
急急急！！！Top

4 楼icecream（）回复于 2001-06-06 19:53:00 得分 0

可以这样写:
query.close;
query.sql.clear;
query.sql.add('select * from tablename where '+edit1.text+'='''+edit2.text+'''');
query.open;
Top

5 楼hxshanji（洪兴山鸡）回复于 2001-06-06 20:16:00 得分 0

哦，我不是很看的懂你的
字段：客户名称产品1数量产品2数量产品2数量产品2数量……
字段值： 01 100 105 80 130
。。。。

可以用
select idkh,idcp,sum(shuliang) as shuliang from yourtable group by idkh,idcp
按各客户不同产品的数量合计。。。Top

6 楼KongKong（空空如野）回复于 2001-06-06 20:23:00 得分 0

使用TDecisionGrid控件吧Top

7 楼cobi（我是小新）回复于 2001-06-06 20:36:00 得分 0

如果按照hxshanji(山鸡)兄的做法，其实现结果是：

客户名称01 产品1 产品1数量
客户名称01 产品2 产品2数量
客户名称01 产品3 产品3数量
。。。
可能跟你的要求不一样。要实现你要求的列显示的话，应该使用数据暂存。把查询出来的数据再按照客户名称进行处理。可以使用两维动态数组实现。Top

8 楼KongKong（空空如野）回复于 2001-06-06 20:39:00 得分 0

object Form1: TForm1
Left = 192
Top = 107
Width = 696
Height = 480
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object DecisionGrid1: TDecisionGrid
Left = 48
Top = 48
Width = 640
Height = 405
Options = [cgGridLines, cgPivotable]
DefaultColWidth = 100
DefaultRowHeight = 20
CaptionColor = clSilver
CaptionFont.Charset = DEFAULT_CHARSET
CaptionFont.Color = clCaptionText
CaptionFont.Height = -11
CaptionFont.Name = 'MS Sans Serif'
CaptionFont.Style = []
DataColor = clBtnHighlight
DataSumColor = 8421440
DataFont.Charset = DEFAULT_CHARSET
DataFont.Color = clWindowText
DataFont.Height = -11
DataFont.Name = 'MS Sans Serif'
DataFont.Style = []
LabelFont.Charset = DEFAULT_CHARSET
LabelFont.Color = clWindowText
LabelFont.Height = -11
LabelFont.Name = 'MS Sans Serif'
LabelFont.Style = []
LabelColor = clBtnFace
LabelSumColor = clInactiveCaption
DecisionSource = DecisionSource1
Dimensions = <
item
DisplayName = '单位'
FieldName = 'DH'
Color = clNone
Alignment = taCenter
Subtotals = False
end
item
FieldName = 'CP'
Color = clNone
Alignment = taCenter
Subtotals = False
end
item
FieldName = 'SUM OF SL'
Color = clNone
Alignment = taLeftJustify
Subtotals = False
end>
Totals = False
ShowCubeEditor = False
Color = clSilver
GridLineWidth = 1
GridLineColor = 4227200
TabOrder = 0
end
object DecisionQuery1: TDecisionQuery
Active = True
DatabaseName = 'D:\'
SQL.Strings = (
'SELECT DH, CP, SUM( SL )'
'FROM "zxb.DBF"'
'GROUP BY DH, CP')
Left = 288
Top = 104
end
object DecisionSource1: TDecisionSource
DecisionCube = DecisionCube1
ControlType = xtRadioEx
SparseRows = False
SparseCols = False
Left = 280
Top = 160
DimensionCount = 2
SummaryCount = 1
CurrentSummary = 0
SparseRows = False
SparseCols = False
DimensionInfo = (
1
0
1
0
0
2
0
1
0
-1)
end
object DecisionCube1: TDecisionCube
DataSet = DecisionQuery1
DimensionMap = <
item
ActiveFlag = diAsNeeded
FieldType = ftString
Fieldname = 'DH'
BaseName = '"zxb.DBF".DH'
Name = 'DH'
DerivedFrom = -1
DimensionType = dimDimension
BinType = binNone
ValueCount = 2
Active = True
end
item
ActiveFlag = diAsNeeded
FieldType = ftString
Fieldname = 'CP'
BaseName = '"zxb.DBF".CP'
Name = 'CP'
DerivedFrom = -1
DimensionType = dimDimension
BinType = binNone
ValueCount = 5
Active = True
end
item
ActiveFlag = diAsNeeded
FieldType = ftFloat
Fieldname = 'SUM OF SL'
BaseName = '"zxb.DBF".SL'
Name = 'SUM OF SL'
DerivedFrom = -1
DimensionType = dimSum
BinType = binNone
ValueCount = -1
Active = True
end>
ShowProgressDialog = True
MaxDimensions = 5
MaxSummaries = 10
MaxCells = 0
Left = 144
Top = 144
end
end

unit Unit1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
mxstore, mxDB, mxgrid, Grids, DBGrids, Db, DBTables, mxtables;

type
TForm1 = class(TForm)
DecisionQuery1: TDecisionQuery;
DecisionSource1: TDecisionSource;
DecisionGrid1: TDecisionGrid;
DecisionCube1: TDecisionCube;
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.DFM}

end.Top

9 楼sTommy（阿木）回复于 2001-06-07 08:51:00 得分 0

cobi(小新国际)兄：
你切中这个问题的关键，只是使用数据暂存、两维动态数组的具体方法能否告知。
感激不尽！Top

10 楼sTommy（阿木）回复于 2001-06-07 09:41:00 得分 0

敬请不吝赐教！！！Top

11 楼hxshanji（洪兴山鸡）回复于 2001-06-07 12:01:00 得分 0

我想他的意图是构建一个[客户号，产品号]的数组，把相应的值放入后处理。Top

12 楼sTommy（阿木）回复于 2001-06-07 15:29:00 得分 0

感谢hxshanji(山鸡)，但我最后要的结果是一个SQL查询数据集。数组可能不行。不知道SQL
中有没有不几个查询结果组合起来的办法。Top

13 楼hxshanji（洪兴山鸡）回复于 2001-06-07 16:59:00 得分 0

union 是用来合并两个查询的。
但是好象很难用一句查询得到你要的要求。Top

14 楼hxshanji（洪兴山鸡）回复于 2001-06-07 17:07:00 得分 20

select a.name,a.id1num,b.id2num,c.id3num...... from (select name,sum(num) as id1num from yourtable where idhw=1 group by idkh) a,(select name,sum(num) as id2num from yourtable where idhw=2 group by idkh) b,(select name,sum(num) as id2num from yourtable where idhw=2 group by idkh) c,........ where a.name=b.name,b.name=c.name........

这样可以生成你要的表不过太烦了。Top

15 楼sTommy（阿木）回复于 2001-06-08 11:01:00 得分 0

感谢 hxshanji(山鸡)　兄：
阁下的精湛技术及热心令在下感动不已！给分！！！
如有闲暇，可与我联系 sun_tommy@china.comTop

16 楼hxshanji（洪兴山鸡）回复于 2001-06-08 11:29:00 得分 0

我的hxshanji@263.netTop