决策数据集的查询问题[请求]

suny

使用决策数据集查询年龄段人数
原始sql语句如下
SELECT GDHR_JBXXSJB.AGE, GDHR_JBXXSJB.DW, GDHR_JBXXSJB.BM
  FROM GDHR_JBXXSJB

使用决策数据集的自定义维设置，设置完成后系统生成的sql语句如下
SELECT '30岁以下' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((GDHR_JBXXSJB.AGE <= 30))
UNION
SELECT '31-35岁' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((NOT(GDHR_JBXXSJB.AGE <= 30)AND (GDHR_JBXXSJB.AGE > 30 and GDHR_JBXXSJB.AGE <= 35)))
UNION
SELECT '36-40岁' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((NOT(GDHR_JBXXSJB.AGE <= 30) AND NOT(GDHR_JBXXSJB.AGE > 30 and GDHR_JBXXSJB.AGE <= 35)AND (GDHR_JBXXSJB.AGE > 36 and GDHR_JBXXSJB.AGE <= 40)))
UNION
SELECT '41-45岁' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((NOT(GDHR_JBXXSJB.AGE <= 30) AND NOT(GDHR_JBXXSJB.AGE > 30 and GDHR_JBXXSJB.AGE <= 35) AND NOT(GDHR_JBXXSJB.AGE > 36 and GDHR_JBXXSJB.AGE <= 40)AND (GDHR_JBXXSJB.AGE > 40 and GDHR_JBXXSJB.AGE <= 45)))
UNION
SELECT '46-50岁' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((NOT(GDHR_JBXXSJB.AGE <= 30) AND NOT(GDHR_JBXXSJB.AGE > 30 and GDHR_JBXXSJB.AGE <= 35) AND NOT(GDHR_JBXXSJB.AGE > 36 and GDHR_JBXXSJB.AGE <= 40) AND NOT(GDHR_JBXXSJB.AGE > 40 and GDHR_JBXXSJB.AGE <= 45)AND (GDHR_JBXXSJB.AGE > 46 and GDHR_JBXXSJB.AGE <= 50)))
UNION
SELECT '51-55岁' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((NOT(GDHR_JBXXSJB.AGE <= 30) AND NOT(GDHR_JBXXSJB.AGE > 30 and GDHR_JBXXSJB.AGE <= 35) AND NOT(GDHR_JBXXSJB.AGE > 36 and GDHR_JBXXSJB.AGE <= 40) AND NOT(GDHR_JBXXSJB.AGE > 40 and GDHR_JBXXSJB.AGE <= 45) AND NOT(GDHR_JBXXSJB.AGE > 46 and GDHR_JBXXSJB.AGE <= 50)AND (GDHR_JBXXSJB.AGE > 50 and GDHR_JBXXSJB.AGE <= 55)))
UNION
SELECT '56-60岁' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((NOT(GDHR_JBXXSJB.AGE <= 30) AND NOT(GDHR_JBXXSJB.AGE > 30 and GDHR_JBXXSJB.AGE <= 35) AND NOT(GDHR_JBXXSJB.AGE > 36 and GDHR_JBXXSJB.AGE <= 40) AND NOT(GDHR_JBXXSJB.AGE > 40 and GDHR_JBXXSJB.AGE <= 45) AND NOT(GDHR_JBXXSJB.AGE > 46 and GDHR_JBXXSJB.AGE <= 50) AND NOT(GDHR_JBXXSJB.AGE > 50 and GDHR_JBXXSJB.AGE <= 55)AND (GDHR_JBXXSJB.AGE > 55 and GDHR_JBXXSJB.AGE <= 60)))
UNION
SELECT '60岁以上' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((NOT(GDHR_JBXXSJB.AGE <= 30) AND NOT(GDHR_JBXXSJB.AGE > 30 and GDHR_JBXXSJB.AGE <= 35) AND NOT(GDHR_JBXXSJB.AGE > 36 and GDHR_JBXXSJB.AGE <= 40) AND NOT(GDHR_JBXXSJB.AGE > 40 and GDHR_JBXXSJB.AGE <= 45) AND NOT(GDHR_JBXXSJB.AGE > 46 and GDHR_JBXXSJB.AGE <= 50) AND NOT(GDHR_JBXXSJB.AGE > 50 and GDHR_JBXXSJB.AGE <= 55) AND NOT(GDHR_JBXXSJB.AGE > 55 and GDHR_JBXXSJB.AGE <= 60)AND (GDHR_JBXXSJB.AGE > 60)))
系统生成的语句中带union ,如果不带union,可以使用sqlfilter来过滤，像这种情况请问如何过滤查询啊

aDragon

建议你在原始SQL上面进行过滤，然后再决策，而不是决策后再过滤

suny

就是在原始sql 上过滤的，代码是这样的
TSQLDataSet(DataSetBrokerage30.DataSet).SQLFilter:='DW like '''+DataSetBrokerdept.DataSet.FieldByName('ZZJGBH').AsString+'%'+''' ';
TSQLDataSet(DataSetBrokerage30.DataSet).SQLFiltered:=true;

但是报错；

aDragon

DataSetBrokerage30.DataSet是决策数据集？
那为何转成TSQLDataSet类型呢？

suny

DataSetBrokerage30.DataSet是决策数据集，转类型是为了加上sqlfilter,但是报错；向这种情况如何加过滤条件啊

aDragon

转成这个类型TDecisionDataSet

suny

和转成TSQLDataSet一样，也是报错。
说明一下，如果构造出的决策数据集sql语句是这个
SELECT '30岁以下' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((GDHR_JBXXSJB.AGE <= 30))
可以正常过滤；

如果再加上一个自定义维，决策数据集的sql语句是这个
SELECT '30岁以下' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((GDHR_JBXXSJB.AGE <= 30))
UNION
SELECT '31-35岁' AS "年龄段", COUNT(GDHR_JBXXSJB.age) AS "个数_年龄"
  FROM GDHR_JBXXSJB
  WHERE (GDHR_JBXXSJB.RYZT like 'A0061%') AND ((NOT(GDHR_JBXXSJB.AGE <= 30)AND (GDHR_JBXXSJB.AGE > 30 and GDHR_JBXXSJB.AGE <= 35)))

数据过滤就报错了；

aDragon

加自定义维的SQL是这样的：
SELECT Name AS "客户名称", Kind AS "客户类别", '111' AS "自定义维1", SUM(CRM_CUSTOMER.PersonalAmount) AS "合计_员工数(人)"
  FROM CRM_CUSTOMER
  WHERE (CRM_CUSTOMER.KIND = '1')
  GROUP BY Name, Kind
不知你是怎么做出UNION的，

suny

我有重新发了个图片，定义多个自定义维后就出现union了

aDragon

SELECT Name AS "客户名称", Kind AS "客户类别", '111' AS "自定义维1", '21321' AS "自定义维2", '2323' AS "自定义维3", '2323' AS "自定义维4", '2323' AS "自定义维5", '23233' AS "自定义维6", '343' AS "自定义维7", '343' AS "自定义维8", '3434' AS "自定义维9", SUM(CRM_CUSTOMER.PersonalAmount) AS "合计_员工数(人)", COUNT(CRM_CUSTOMER.PersonalAmount) AS "个数_员工数(人)", AVG(CRM_CUSTOMER.PersonalAmount) AS "统计平均值_员工数(人)"
  FROM CRM_CUSTOMER
  WHERE (((((((((CRM_CUSTOMER.KIND = '1')) AND ((CRM_CUSTOMER.KIND = '2'))) AND ((CRM_CUSTOMER.NAME = '1'))) AND ((CRM_CUSTOMER.KIND = '2'))) AND ((CRM_CUSTOMER.NAME = '1'))) AND ((CRM_CUSTOMER.KIND = '2'))) AND ((CRM_CUSTOMER.KIND = '3434'))) AND ((CRM_CUSTOMER.KIND = '34'))) AND ((CRM_CUSTOMER.KIND = '343'))
  GROUP BY Name, Kind

我定义了很多的自定义维也没有出现这个现象，请问你用的是哪个版本呢？