使用Group by的效率问题

Question

A.DD_NO = B.DD_NO  上建立索引~~

Answer 1

忘了说了，DD_NO是主键，而且已建立索引。

Answer 2

--你这语句好像有问题
SELECT A.DD_NO,A.MX_NO,A.NAME,B.FLG 
FROM t_DD A 
INNER JOIN 
( 
  SELECT DD_NO,MAX(FLG) AS FLG 
  FROM t_DD 
  GROUP BY DD_NO 
  HAVING MIN(FLG) > 0 
) B 
WHERE A.DD_NO = B.DD_NO SELECT A.DD_NO,A.MX_NO,A.NAME,B.FLG 
FROM t_DD A 
INNER JOIN 
( 
  SELECT DD_NO,MAX(FLG) AS FLG 
  FROM t_DD 
  GROUP BY DD_NO 
  HAVING MIN(FLG) > 0 
) B ON A.DD_NO = B.DD_NO 

Answer 3

--> By dobear_0922(小熊) 2008-11-13 14:55:08
--> 测试数据：[t_DD]
if object_id('[t_DD]') is not null drop table [t_DD]
create table [t_DD]([DD_NO] varchar(5),[MX_NO] varchar(5),[Name] varchar(4),[FLG] int)
insert [t_DD]
select '00001','00001','毛笔',0 union all
select '00001','00002','钢笔',1 union all
select '00001','00003','铅笔',8 union all
select '00002','00001','毛笔',1 union all
select '00002','00002','钢笔',1 union all
select '00002','00003','铅笔',8 union all
select '00003','00001','毛笔',1 union all
select '00003','00002','钢笔',1 union all
select '00003','00003','铅笔',1select DD_NO, MX_NO, [NAME], FLG=max(FLG) over(partition by DD_NO)
from [t_DD] t
where not exists(select * from [t_DD] where DD_NO=t.DD_NO and FLG=0)
/*
DD_NO MX_NO NAME FLG
----- ----- ---- -----------
00002 00001 毛笔   8
00002 00002 钢笔   8
00002 00003 铅笔   8
00003 00001 毛笔   1
00003 00002 钢笔   1
00003 00003 铅笔   1(6 行受影响)
*/
drop table [t_DD]

Answer 4

--> By dobear_0922(小熊) 2008-11-13 14:55:08
--> 测试数据：[t_DD]
if object_id('[t_DD]') is not null drop table [t_DD]
create table [t_DD]([DD_NO] varchar(5),[MX_NO] varchar(5),[Name] varchar(4),[FLG] int)
insert [t_DD]
select '00001','00001','毛笔',0 union all
select '00001','00002','钢笔',1 union all
select '00001','00003','铅笔',8 union all
select '00002','00001','毛笔',1 union all
select '00002','00002','钢笔',1 union all
select '00002','00003','铅笔',8 union all
select '00003','00001','毛笔',1 union all
select '00003','00002','钢笔',1 union all
select '00003','00003','铅笔',1select A.DD_NO, A.MX_NO, A.[NAME], FLG=max(FLG) over(partition by A.DD_NO)
from [t_DD] A left join (select DD_NO from [t_DD] where FLG=0 ) B
on A.DD_NO=B.DD_NO
where B.DD_NO is null
/*
DD_NO MX_NO NAME FLG
----- ----- ---- -----------
00002 00001 毛笔   8
00002 00002 钢笔   8
00002 00003 铅笔   8
00003 00001 毛笔   1
00003 00002 钢笔   1
00003 00003 铅笔   1(6 行受影响)
*/
drop table [t_DD]

Answer 5

请问，你这种写法是SQLServer2005，还是Oracle? 我用的是SQLserver2000，
“FLG=max(FLG) over(partition by A.DD_NO)”
这句不认。

Answer 6

因为是维护旧系统，比较老，所以只能是SQLServer2000，那有没有人有其它的好办法呢？

Answer 7

如果‘确认flg’只有 这三种状态
考虑，不用group，考虑使用表变量或临时表
是否更快速，主要看数据量大小，数据分布情况。好像还逃不脱多次扫描，我想主要考虑是否使用group
,怎样去join是关键

Answer 8

方法比较老土，just for fun...
if object_id('[t_dd]') is not null drop table [t_dd]
go
create table [t_dd]([dd_no] varchar(5),[mx_no] varchar(5),[name] varchar(4))
insert [t_dd]
select '00001','00001','毛笔' union all
select '00001','00002','钢笔'  union all
select '00001','00003','铅笔'  union all
select '00002','00001','毛笔'  union all
select '00002','00002','钢笔'  union all
select '00002','00003','铅笔'  union all
select '00003','00001','毛笔'  union all
select '00003','00002','钢笔'  union all
select '00003','00003','铅笔' 
if object_id('[t_MM]') is not null drop table [t_MM]
go
create table [t_MM]([dd_no] varchar(5),[mx_no] varchar(5),[user] varchar(10),[flg] int)
insert [t_MM]
select '00001','00001','法师',0 union all
select '00001','00002','盗贼',1 union all
select '00001','00003','战士',8 union all
select '00002','00001','术士',1 union all
select '00002','00002','萨满',1 union all
select '00002','00003','圣骑',8 union all
select '00003','00001','牧师',1 union all
select '00003','00002','猎人',1 union all
select '00003','00003','德鲁伊',1
--select * from [t_dd]
--select * from [t_MM]
select a.dd_no,a.mx_no,a.name,b.[user],b.flg
from [t_dd] a join
(
select dd_no,mx_no,[user],flg=(select max(flg) from [t_MM] where dd_no=t.dd_no)
from [t_MM] t
where not exists(select 1 from [t_MM] where dd_no=t.dd_no and flg=0)
) b
on a.dd_no=b.dd_no and a.mx_no=b.mx_no --测试结果:
/*
dd_no mx_no name   user       flg
----- ----- ---- ---------- -----------
00002 00001 毛笔   术士         8
00002 00002 钢笔   萨满         8
00002 00003 铅笔   圣骑         8
00003 00001 毛笔   牧师         1
00003 00002 钢笔   猎人         1
00003 00003 铅笔   德鲁伊       1(6 row(s) affected)
*/

Answer 9

DD_NO怎么会是主键呢，不是有重复值吗? 

Answer 10

经测试，在SQL2008下测试16楼的sql code结果如下<繁体xp+sp2+sql2008>：
Note:简体字在繁体操作系统下乱码则忽略!00002 00001 毛? ?士 8
00002 00002 ?? ?? 8
00002 00003 ?? 圣? 8
00003 00001 毛? 牧? 1
00003 00002 ?? ?人 1
00003 00003 ?? 德?伊 1
(6 個資料列受到影響)

Answer 11

为什么不先通过Flg过滤t_MM呢，然后在建立和t_DD的关系。这样不就减少一次的扫描了嘛

Answer 12

怎么可能少一次呢？ ,MAX(FLG) AS FLG 
结果中需要这个聚合值，能不取吗?如果不先聚合再连表，而是直接单表过滤聚合，又如何过滤呢? 用where 过滤？ 那么不符条件的记录组也将被选取（除了flag不合规则的那条)
如果用having过滤，不又回到要取其它列的问题上了?

Answer 13

这个很正常，他用的varchar当数据需要在多环境下使用时用nvarchar,赋值时以N'为前辍。可以参考此贴http://topic.csdn.net/u/20081111/16/b4ec2e0e-68ad-46da-9605-670d89e6313d.html

Answer 14

能不能把t_MM和t＿DD的表結構貼出來？要不這樣想優化，只能是原則上的優化，很難具體到SQL語句上的優化吧？

Answer 15

呵呵，好几个人参与哦。本来想结贴的，那就应22楼的说法，写一下表结构吧。17楼眼很尖哦，我这里只是把原来的表做了一个简化，本以为能描述清楚就行了，看来，还是漏了些东西。好，真实表结构如下：CREATE TABLE [dbo].[t_dd] (
[company_cd] [varchar] (10) ,
[dd_no] [char] (14) ,
[group_no] [char] (5) ,
[mx_no] [char] (2) ,
[shp_cd] [char] (10) NOT NULL ,
[shp_type] [char] (5) NOT NULL ,
[shp_count] [int] NOT NULL ,
[qr_flg] [char] (1) ,
[last_update_date] [datetime] NULL ,
[last_update_cd] [varchar] (16) ,
[last_update_name] [varchar] (50)
) ON [PRIMARY]
GOALTER TABLE [dbo].[t_dd] WITH NOCHECK ADD 
CONSTRAINT [PK_t_dd] PRIMARY KEY  CLUSTERED 
(
[company_cd],
[dd_no],
[group_no],
[mx_no]
)  ON [PRIMARY] 
GO CREATE  INDEX [p_t_dd] ON [dbo].[t_t_dd]([company_cd], [dd_no], [group_no], [mx_no]) ON [PRIMARY]
GOCREATE TABLE [dbo].[t_train_answer] (
[company_cd] [varchar] (10) ,
[dd_no] [char] (14) ,
[group_no] [char] (5) ,
[mx_no] [char] (2) ,
[answer] [char] (1),
[answerer] [varchar] (20)
) ON [PRIMARY]
GOALTER TABLE [dbo].[t_train_answer] WITH NOCHECK ADD 
CONSTRAINT [PK_t_train_answer] PRIMARY KEY  CLUSTERED 
(
[company_cd],
[dd_no],
[group_no],
[mx_no]
) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO CREATE  INDEX [IX_t_train_answer] ON [dbo].[t_t_train_answer](]([company_cd], [dd_no], [group_no], [mx_no]) ON [PRIMARY]
GO
说明:
 1. qr_flg 确认flg （0/未确认， 1/已确认）
 2. answer 回答     （0/未回答， 1/正常， 8/错误）
 3. 回答与订单明细是一一对应的，但是可以只回答订单的一部分，甚至仅一条明细，所以，回答表并不一定包含所有的明细。
 4. 检索目标是：公司A的所有明细都回答了的且未确认的订单。检索内容：订单表的所有及回答表中的回答者。
有兴趣的讨论下吧。