奔跑的小鱼儿 2017-11-21 00:51 采纳率: 100%
浏览 1416
已采纳

如何写sql语句，给个思路也行，记录表有几千万条记录，产品表有几百万条，人员表有几百条

记录表：

 CREATE TABLE [dbo].[wsd_count](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [uid] [varchar](64) NULL,
    [line] [varchar](64) NULL,
    [num] [varchar](64) NULL,
    [result] [varchar](64) NULL,
    [data] [datetime] NULL,
    [count_reserve1] [nvarchar](255) NULL,
    [count_reserve2] [nvarchar](255) NULL,
    [count_reserve3] [nvarchar](255) NULL,
    [count_reserve4] [nvarchar](255) NULL,
    [count_reserve5] [datetime] NULL,
 CONSTRAINT [PK_wsd_count] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'uid'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产线' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'line'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'num'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结果' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'result'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'data'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve5'
GO

产品表：

 CREATE TABLE [dbo].[wsd_product](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [num] [varchar](64) NULL,
    [pmname] [varchar](64) NULL,
    [prname] [varchar](64) NULL,
    [scdata] [datetime] NULL,
    [zsdata] [datetime] NULL,
    [type] [varchar](64) NULL,
    [color] [varchar](64) NULL,
    [ptlot] [varchar](64) NULL,
    [car] [varchar](64) NULL,
    [remake] [varchar](64) NULL,
    [product_reserve1] [nvarchar](255) NULL,
    [product_reserve2] [nvarchar](255) NULL,
    [product_reserve3] [nvarchar](255) NULL,
    [product_reserve4] [nvarchar](255) NULL,
    [product_reserve5] [nvarchar](255) NULL,
    [product_reserve6] [nvarchar](255) NULL,
    [product_reserve7] [nvarchar](255) NULL,
    [product_reserve8] [datetime] NULL,
    [product_reserve9] [datetime] NULL,
    [product_reserve0] [datetime] NULL,
 CONSTRAINT [PK_wsd_product] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'件号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'num'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'pmname'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'prname'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'scdata'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注塑日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'zsdata'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品分类' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'type'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'颜色' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'color'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'喷涂批次' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'ptlot'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'车号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'car'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'remake'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve5'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve6'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段7' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve7'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段8' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve8'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段9' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve9'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve0'
GO

人员表：

 CREATE TABLE [dbo].[sys_user](
    [id] [varchar](64) NOT NULL,
    [company_id] [varchar](64) NOT NULL,
    [office_id] [varchar](64) NOT NULL,
    [login_name] [varchar](100) NOT NULL,
    [password] [varchar](100) NOT NULL,
    [no] [varchar](100) NULL,
    [name] [varchar](100) NOT NULL,
    [email] [varchar](200) NULL,
    [phone] [varchar](200) NULL,
    [mobile] [varchar](200) NULL,
    [user_type] [char](1) NULL,
    [photo] [varchar](1000) NULL,
    [login_ip] [varchar](100) NULL,
    [login_date] [datetime] NULL,
    [login_flag] [varchar](64) NULL,
    [create_by] [varchar](64) NOT NULL,
    [create_date] [datetime] NOT NULL,
    [update_by] [varchar](64) NOT NULL,
    [update_date] [datetime] NOT NULL,
    [remarks] [varchar](255) NULL,
    [del_flag] [char](1) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[sys_user] ADD  DEFAULT ('0') FOR [del_flag]
GO

背景：某零件加工厂有几条产线检测产品是否合格，每检测一件产品在记录表中添加一条记录，检测结果放到result字段中，检测结果有四个大类，三十多小类，详情如下：

合格抛光打磨报废
打磨流挂
打磨颗粒。。。
要求：
统计某个员工某个时间段做的某个产品的统计信息，包括员工个人信息，一次合格率（合格/总数），二次合格率（（合格+抛光*0.85）/所有），各个检测结果的数量。
查询条件为空时列出所有员工，所做的所有产品的统计记录，每个产品单独占一行。

使用sqlsever2008，查询百万数据速度在三秒内可增加悬赏。

  • 写回答

6条回答 默认 最新

  • 软件求生 2017-11-21 07:57
    关注

    用临时表代替游标，你试试。
    不过3秒不行，你这判断条件基本都是走全表的。
    你可以逐条看看哪一块慢，进行针对优化 。
    而且你可以看看是否有哪些是无用的。
    你给各个关键点加索引。

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(5条)

报告相同问题？