|
|
楼主 |
发表于 2009-6-23 12:30
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
很高兴能有几个拿满分，尤其是小蚁雄兵。代码简洁，所以给加了1分。
我这儿来给大家总结一下。
我借用了C++的编代码方式，重新布局了一下SQL语句，带给你们一种新的写代码的方式，直观，可以对不同查询结果进行分段测试。这样写的代码可以直接往窗体中贴进去。
1、小蚁雄兵：OLE DB 查询方式，取条件再取最小法
select t1.sales,
t4.month&"_"&t4.monthname as `month`,
left(namesex,instr(1,namesex," ",1)-1) as firstname,
mid(namesex,instr(1,namesex," ",1)+1,instr(1,namesex,"-",1)-instr(1,namesex," ",1)-1) as lastname,
right(namesex,len(namesex)-instr(1,namesex,"-",1)) as sex,
(
select min(commission) from [sheet3$] t3 where t3.sales>t1.sales
) as 分段提成,
(
select max(commission) from [sheet3$]) as 最大提成,
iif
(
分段提成>0,
分段提成,
最大提成
) as commission,
commission*sales as salary
from [Sheet1$] t1 , [sheet2$] t2,[sheet4$] t4
where t1.staffid=t2.staffid and t1.month=t4.month
蓝色代码
select max(commission) from [sheet3$]) as 最大提成
是可以调试的
而
select min(commission) from [sheet3$] t3 where t3.sales>t1.sales
是不能抽出来调试的，因为条件中用到外层的t1 表的数据
他的代码就用了一层嵌套，其他的表用条件就可以提取我们需要的结果。
2、dcw0402：OLE DB 查询方式
结果错，提成取值错误，同时代码重复，红色部分标识
组合文本不用加括号，绿色标识
select [sheet1$].sales,
(
[sheet1$].month & '_'& [sheet4$].monthname
) as [month],
mid([sheet2$].namesex,1,instr([sheet2$].namesex,' ')-1) as firstname,
mid([sheet2$].namesex,instr([sheet2$].namesex,' ')+1,instr([sheet2$].namesex,'-')-instr([sheet2$].namesex,' ')-1) as lastname,
right([sheet2$].namesex,len([sheet2$].namesex)-instr([sheet2$].namesex,'-')) as sex,
(
[sheet1$].sales *
iif
(
[sheet1$].sales>=(select min([sheet3$].sales) from [sheet3$]),
iif
(
[sheet1$].sales>(select max([sheet3$].sales) from [sheet3$]),
(select max([sheet3$].commission) from [sheet3$]),
(select max([sheet3$].commission) from [sheet3$] where [sheet3$].sales <= [sheet1$].sales)
),
(select min([sheet3$].commission) from [sheet3$]))
)
as salary,
iif
(
[sheet1$].sales>=(select min([sheet3$].sales) from [sheet3$]),
iif
(
[sheet1$].sales>(select max([sheet3$].sales) from [sheet3$]),
(select max([sheet3$].commission) from [sheet3$]),
(select max([sheet3$].commission) from [sheet3$] where [sheet3$].sales <= [sheet1$].sales)
),
(select min([sheet3$].commission) from [sheet3$])
)
as commission
from
[sheet1$],[sheet4$],[sheet2$]
where [sheet1$].month=[sheet4$].month and [sheet1$].StaffID = [sheet2$].StaffID
3、shlzml：OLE DB 查询方式 采用分段计算组合法
要提示的 组合文本没必要加括号 绿色标识
SELECT
(
`Sheet4$`.month & '_' & `Sheet4$`.monthname
) as month_,
(
left(`Sheet2$`.namesex,instr(1,`Sheet2$`.namesex,' ',1)-1)
) as firstname,
(
mid(`Sheet2$`.namesex,instr(1,`Sheet2$`.namesex,' ',1)+1,instr(1,`Sheet2$`.namesex,'-',1)-instr(1,`Sheet2$`.namesex,' ',1)-1)
) as lastname,
(
right(`Sheet2$`.namesex,len(`Sheet2$`.namesex)-instr(1,`Sheet2$`.namesex,'-',1))
) as sex,
(
(0.0002*Count(`Sheet1$`.sales)+0.002)*`Sheet1$`.sales
) as salary,
(
iif
(
(0.0002*(Count(`Sheet1$`.sales))+0.002)<=(select max(t.commission) from `Sheet3$` t),
(0.0002*(Count(`Sheet1$`.sales))+0.002),
(select max(t.commission) from `Sheet3$` t)
)
)
as commission,
`Sheet1$`.sales
FROM `Sheet1$`,`Sheet2$`, `Sheet3$`, `Sheet4$`
WHERE `Sheet3$`.sales <= `Sheet1$`.sales AND `Sheet1$`.StaffID = `Sheet2$`.StaffID AND `Sheet1$`.month = `Sheet4$`.month
GROUP BY `Sheet4$`.month, `Sheet4$`.monthname, `Sheet2$`.namesex, `Sheet1$`.sales
union all
SELECT
(
`Sheet4$`.month & '_' & `Sheet4$`.monthname
),
left(`Sheet2$`.namesex,instr(1,`Sheet2$`.namesex,' ',1)-1),
mid(`Sheet2$`.namesex,instr(1,`Sheet2$`.namesex,' ',1)+1,instr(1,`Sheet2$`.namesex,'-',1)-instr(1,`Sheet2$`.namesex,' ',1)-1),
right(`Sheet2$`.namesex,len(`Sheet2$`.namesex)-instr(1,`Sheet2$`.namesex,'-',1)),
(0.002*`Sheet1$`.sales),
0.002,
`Sheet1$`.sales
FROM `Sheet1$`,`Sheet2$`, `Sheet3$`, `Sheet4$`
WHERE `Sheet1$`.sales <(select min(`Sheet3$`.sales) from `Sheet3$`) AND `Sheet1$`.StaffID = `Sheet2$`.StaffID AND `Sheet1$`.month = `Sheet4$`.month
GROUP BY `Sheet4$`.month, `Sheet4$`.monthname, `Sheet2$`.namesex, `Sheet1$`.sales、
这个代码看起来就很累，红色部分用t重命名了工资表sheet3，很是多余，
仅仅用一个表时，我们没必要在字段前添加表名，而只有在不同表中有相同字段时，我们才在相同字段前加表名以示区别，就是有很多表如果没有相同字段，我们都可以直接引用每个字段而不必添加表名。
4、fdd：新建数据库查询sql_query方式
采用嵌套组合方式，
初看结果对，但很是遗憾
你试试如果月份是11 StaffID为1 和月份是1而StaffID为11 这样结果就会出错，要组合就得加一个什么字符避免出现组合相同的情况出现。聪明反被聪明误。
就以你的语句添上颜色，一层一层调试查询结果，以蓝色为最里层，然后是紫色，再就是棕色
用数据导入方式在OLE DB窗体中放SQL语句，确定直接查询出数据记录。从里层往外层逐层逐层调试，你试试看。
select x.StaffID,x.sales,x.firstname,x.lastname,x.sex,
iif
(
x.month<10,
' '&x.month,
x.month
)&'-'&x.monthname as month,
y.mincommission as commission,
x.sales * y.mincommission as salary
from
(
select c.*,
d.monthname
from
(
select a.*,
b.firstname,b.lastname,b.sex
from [sheet1$] a,
(
select StaffID,
left(namesex,instr(namesex,' ')) as firstname,
mid(namesex,instr(namesex,' ')+1,instr(namesex,'-')-instr(namesex,' ')-1) as lastname,
right(namesex,len(namesex)-instr(namesex,'-')) as sex
from [sheet2$][/colo]
) b
where a.StaffID=b.StaffID
)
c,
[sheet4$] d
where c.month=d.month
) x,
(
select ID,min(commission) as mincommission
from
(
select a.month&a.StaffID as ID,*
from [sheet1$] a,
(
select
iif
(
isnull(sales),
(9e+307)*1,
sales
) as sales_,
commission
from [sheet3$]
) b
where a.sales<b.sales_
)
group by ID
) y
where x.month&x.StaffID=y.ID
5、冻豆腐：OLE DB 查询方式，公式计算法，利用了提成系数的规律
蓝色部分重复，第二个蓝色部分可以用commission代替
重命名字段如果中间没有空格可以省略引号的
SELECT
distinct mid(`Sheet2$`.namesex,1,instr(`Sheet2$`.namesex,' ')-1) as 'firstname' ,
mid(`Sheet2$`.namesex,instr(`Sheet2$`.namesex,' ')+1,instr(`Sheet2$`.namesex,'-')-instr(`Sheet2$`.namesex,' ')-1) as 'lastname' ,
mid(`Sheet2$`.namesex,instr(`Sheet2$`.namesex,'-')+1) as 'sex' ,
`Sheet1$`.sales as 'sales' ,
(
select count(*) from `Sheet3$` where `Sheet3$`.sales<=`Sheet1$`.sales
)*0.0002+0.002 as 'commission',
`Sheet4$`.month&'_'&`Sheet4$`.monthname as 'month',
(
(select count(*) from `Sheet3$` where `Sheet3$`.sales<=`Sheet1$`.sales
)*0.0002+0.002)*`Sheet1$`.sales as 'salary'
FROM `Sheet1$`, `Sheet2$`, `Sheet3$`,`Sheet4$`
WHERE `Sheet1$`.StaffID = `Sheet2$`.StaffID AND `Sheet1$`.month = `Sheet4$`.month
6、monvzhilei：OLE DB 查询方式select top 1 commission
这里用得好
代码可以简化
select mont as [month],
commission,sales,firstname,lastname,sex,sales*commission as salary
from
(select month,StaffID,sales,
IIf
(
aa.comm is null,
(
select commission from [Sheet3$] where sales is null
),
aa.comm
) AS commission
from
(
select month,StaffID,sales1 as [sales],
(
select top 1 commission
from [Sheet3$] T3
where sales>a.sales1
order by T3.sales
) as comm
from
(
SELECT month,StaffID, sum(sales) as [sales1]
FROM [Sheet1$] T1
group by month,staffID
) A
) AA
) BB,
(
select staffID,mid(namesex,1,instr(namesex,' ')-1) as firstname,
mid(namesex,instr(namesex,' '),instr(namesex,'-')-instr(namesex,' ')) as lastname, mid(namesex,instr(namesex,'-')+1,100) as sex
from [sheet2$]
) CC,
(
select month,month&'_'&monthname as mont
from [sheet4$] T4
) DD
where BB.staffid=CC.staffid and BB.month=DD.month
几点小贴士：
1、代码的书写以方便逐层调试的方法来写
上面已经介绍
2、浮点问题
生成对应提成比率的最小销售、最大销售：
SELECT 0 AS minsales, sales as maxsales, commission as com
FROM [Sheet3$]
WHERE commission=(select min(commission) from [Sheet3$])
union all
select minsales,sales as maxsales, com
from
(
SELECT sales AS minsales, commission+0.0002 as com
FROM [Sheet3$]
) b,[Sheet3$]
where round(com,4)=round(commission,4) and sales>0
union all
SELECT sales, 9000000000,
commission+0.0002 as com
FROM [Sheet3$]
WHERE commission=
(
select max(commission)
from
[Sheet3$]
)-0.0002
在截取中间段数据时
如果用下面这个做就会丢失记录：
select minsales,sales as maxsales, com
from
(
SELECT sales AS minsales, commission+0.0002 as com
FROM [Sheet3$]
) b,[Sheet3$]
where com=commission and sales>0
这是因为浮点数的问题造成的，不是因为数据处理过程中取小数位造成的。
我们用函数round就按保留4位小数位四舍五入了。
多年以前，我曾经在很多单元格中输入0，然后求和，然后将列宽拉开小数位增加，这样就看到有数据了，我还以为是office本身的bug。
在SQL server 数据库中在浮点字段记录中输入整数，其结果显示就不是整数。例如输入12，显示的可能就是11.99999.。。。 或12.0000。。。后面拖着一串数字。
刚开始出题时本意想来点浮点数的，后来组合就成了上期的竞赛题。
3、 Top的技巧用法
假如你想要提取某学校学生101名到200名之间的记录，你怎么做？
抑或是按100名顺序来分组做成绩分析，也就是前100名的成绩统计，101到200归到第2组的成绩统计，201到300归到第3组的成绩统计，。。。依次类推
前100名就用
select Top 100 * from table order by 字段1 desc，字段2 desc，。。。
n*100+1到n*100+100名:
select Top 100 *
from
(
select Top count（*)-100*n *
from table order by 字段1 ，字段2 ，。。。
)
order by 字段1 desc，字段2 desc，。。。
desc和asc是一对
desc 是descend 降序意思
asc 是ascend 升序的意思
asc在字段后面可以省略
内层查询是升序排列后去前总记录数减去n*100行的记录，然后对查询表降序再取前100名记录
这样就可以达到第n+1个分组段的记录，
升序和降序要配套使用，你可以变化很多查询结果出来
查询分组后在添加组别字段名用union all 组合起来，我们就可以用数据透视表表进行分析数据。
而在组合前进行聚合函数出来，还可以实现各个分段记录更详细的分析。
据说在查询语句中加上TOP还能加速SQL的执行速度。
[ 本帖最后由 Scarlett_88 于 2009-6-24 02:52 编辑 ] |
评分
-
1
查看全部评分
-
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-6-1 09:38
