SHAPE ( SHAPE {SELECT * FROM `Customers`} AS Customers APPEND (( SHAPE {SELECT * FROM `Orders`} AS Orders APPEND (( SHAPE {select OrderID,ProductID,UnitPrice,Quantity,(Quantity*UnitPrice) as Total from [Order Details]} AS OrderDetails APPEND ({SELECT * FROM `Products`} AS Products RELATE 'ProductID' TO 'ProductID') AS Products) AS OrderDetails RELATE 'OrderID' TO 'OrderID') AS OrderDetails, SUM(OrderDetails.'ProductID') AS ProductCount) AS Orders RELATE 'CustomerID' TO 'CustomerID') AS Orders) COMPUTE Customers BY 'Country'
其实我都是在VB里用一个集合来把运算符放到一起,例如: Dim A As Collection, B As Collection Dim SQL As StringSet A = New Collection Set B = New Collection A.Add "id" A.Add "name" A.Add "idcard" A.Add "works" A.Add "tel" A.Add "cash" B.Add "=" B.Add ">" B.Add "<" B.Add "like"If cobObj.ListIndex = 0 Or cobObj.ListIndex = 5 Then SQL = "select id,name,dot,createdate,idcard,works,tel,cash,(select count(a.cardid) from consume as a where a.cardid=b.id) from card as b where " _ & "" & A(cobObj.ListIndex + 1) & " " & B(cobOpr.ListIndex + 1) & " " & txtValue.Text Else If cobOpr.ListIndex = 3 Then SQL = "select id,name,dot,createdate,idcard,works,tel,cash,(select count(a.cardid) from consume as a where a.cardid=b.id) from card as b where " _ & "" & A(cobObj.ListIndex + 1) & " " & B(cobOpr.ListIndex + 1) & "'%" & txtValue.Text & "%'" Else SQL = "select id,name,dot,createdate,idcard,works,tel,cash,(select count(a.cardid) from consume as a where a.cardid=b.id) from card as b where " _ & "" & A(cobObj.ListIndex + 1) & " " & B(cobOpr.ListIndex + 1) & "'" & txtValue.Text & "'" End If End If
http://www.zarr.net
http://www.vbhelp.bet
http://www.21code.com
http://www.vbthunder.com
http://www.easthot.net
http://www.freevbcode.com
http://www.vbaccelerator.com
http://jinesc.6600.org
@in1 varchar(100) output /*可指定多个条件*/
@a1 varchar(10),@a2 varchar(10),@a3 varchar(10),@a4 varchar(10)
@sqlstring valchar(500)select @a1=field1,@a2=field2,@a3=field3 @a4=field4 from tmptable select @sqlstring = 'select * from where fields1' + @a1 + @in1..../*依次累加你的条件,@a1为运算符,@in1为你要查询的条件值*/exec @sqlstring
之后就可以调用这个过程了
exec myquery '12%','小张'
Dim A As Collection, B As Collection
Dim SQL As StringSet A = New Collection
Set B = New Collection
A.Add "id"
A.Add "name"
A.Add "idcard"
A.Add "works"
A.Add "tel"
A.Add "cash"
B.Add "="
B.Add ">"
B.Add "<"
B.Add "like"If cobObj.ListIndex = 0 Or cobObj.ListIndex = 5 Then
SQL = "select id,name,dot,createdate,idcard,works,tel,cash,(select count(a.cardid) from consume as a where a.cardid=b.id) from card as b where " _
& "" & A(cobObj.ListIndex + 1) & " " & B(cobOpr.ListIndex + 1) & " " & txtValue.Text
Else
If cobOpr.ListIndex = 3 Then
SQL = "select id,name,dot,createdate,idcard,works,tel,cash,(select count(a.cardid) from consume as a where a.cardid=b.id) from card as b where " _
& "" & A(cobObj.ListIndex + 1) & " " & B(cobOpr.ListIndex + 1) & "'%" & txtValue.Text & "%'"
Else
SQL = "select id,name,dot,createdate,idcard,works,tel,cash,(select count(a.cardid) from consume as a where a.cardid=b.id) from card as b where " _
& "" & A(cobObj.ListIndex + 1) & " " & B(cobOpr.ListIndex + 1) & "'" & txtValue.Text & "'"
End If
End If