Цитата:
Сообщение от
korvin
Проверил, не совсем то, вернее совсем не то получается, что в задаче
А что не то?
Свиду вроде все нормально.
info вот что выдает:
SELECT * FROM Table1 GROUP BY Table1.StorageId ASC, Table1.WareId ASC, Table1.PartyId ASC WHERE ((StorageId = 000 OR StorageId = 001)) AND ((WareId = 002 OR WareId = 003)) JOIN SUM(AmountMod), SUM(CostMod) FROM Table1 WHERE Table1.StorageId = Table1.StorageId AND Table1.WareId = Table1.WareId
Планы запросов только скобками отличаются:
SELECT A.STORAGEID,A.WAREID,A.PARTYID,SUM(B.AMOUNTMOD),SUM(B.COSTMOD)
FROM TABLE1 A,TABLE1 B
WHERE ((A.DATAAREAID='DAT')
AND (((A.STORAGEID='000') OR (A.STORAGEID='001'))
AND ((A.WAREID='002') OR (A.WAREID='003'))))
AND ((B.DATAAREAID='DAT')
AND ((A.STORAGEID=B.STORAGEID)
AND (A.WAREID=B.WAREID)))
GROUP BY A.STORAGEID,A.WAREID,A.PARTYID
ORDER BY A.STORAGEID,A.WAREID,A.PARTYID OPTION(FAST 22)
SELECT A.STORAGEID,A.WAREID,A.PARTYID,SUM(B.AMOUNTMOD),SUM(B.COSTMOD)
FROM TABLE1 A,TABLE1 B
WHERE (A.DATAAREAID='DAT')
AND ((B.DATAAREAID='DAT')
AND (((((A.STORAGEID='000') OR (A.STORAGEID='001'))
AND ((A.WAREID='002') OR (A.WAREID='003')))
AND (B.STORAGEID=A.STORAGEID))
AND (B.WAREID=A.WAREID)))
GROUP BY A.STORAGEID,A.WAREID,A.PARTYID
ORDER BY A.STORAGEID,A.WAREID,A.PARTYID OPTION(FAST 22)
PHP код:
static void Job39(Args _args)
{
Table1 wp0;
Table1 wp1;
Table1 wp0_q;
Table1 wp1_q;
StorageId xxx = "000";
StorageId yyy = "001";
WareId zzz = "002";
WareId aaa = "003";
Query query = new Query();
QueryBuildDataSource qbds1, qbds2;
QueryRun QueryRun;
;
select * from wp0 group by StorageId, WareId, PartyId
join sum(AmountMod), sum(CostMod) from wp1
where
(wp0.StorageId == xxx || wp0.StorageId == yyy) &&
(wp0.WareId == zzz || wp0.WareId == aaa) &&
wp1.StorageId == wp0.StorageId &&
wp1.WareId == wp0.WareId;
qbds1 = query.AddDataSource(tablenum(Table1));
qbds1.ordermode(ordermode::groupby);
qbds1.addSortField(fieldnum(Table1, StorageId));
qbds1.addSortField(fieldnum(Table1, WareId));
qbds1.addSortField(fieldnum(Table1, PartyId));
qbds1.addrange(fieldnum(Table1, StorageId)).value(xxx + "," + yyy);
qbds1.addrange(fieldnum(Table1, WareId)).value(zzz + "," + aaa);
qbds2 = qbds1.AddDataSource(tablenum(Table1));
qbds2.ordermode(ordermode::groupby);
qbds2.addSelectionField(fieldnum(Table1, AmountMod), selectionField::Sum);
qbds2.addSelectionField(fieldnum(Table1, CostMod), selectionField::Sum);
qbds2.addLink(fieldnum(Table1, StorageId), fieldnum(Table1, StorageId));
qbds2.addLink(fieldnum(Table1, WareId), fieldnum(Table1, WareId));
info(query.dataSourceNo(1).toString());
QueryRun = new QueryRun(query);
QueryRun.next();
wp0_q = QueryRun.getNo(1);
wp1_q = QueryRun.getNo(2);
}