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

如何写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秒不行,你这判断条件基本都是走全表的。
    你可以逐条看看哪一块慢,进行针对优化 。
    而且你可以看看是否有哪些是无用的。
    你给各个关键点加索引。

    点赞 打赏 评论
  • 孤芳不自賞 2017-11-21 02:47

    你需要实时查询还是一段时间查一次?

    点赞 打赏 评论
  • kelin_zhang321 2017-11-21 07:20

    百万数据3秒查询,这有点费劲吧,而且还需要关联表,给表建索引是必不可少的,调用存储过程

    点赞 打赏 评论
  • 奔跑的小鱼儿 2017-11-21 07:31

    存储过程实现:

    ```ALTER PROCEDURE [dbo].[wsdmansum]
    -- Add the parameters for the stored procedure here
    @gh nvarchar(255), @xm nvarchar(255), @cx varchar(64),
    @cp varchar(64), @jg varchar(64), @sdata datetime, @edata datetime
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @moth varchar(64),@weekday varchar(64),@scdata datetime, @zsdata datetime, @jcdata datetime;
    declare @num varchar(64), @pmname varchar(64), @prname varchar(64);
    declare @prtype varchar(64), @color varchar(64), @ptpc varchar(64);
    declare @car varchar(64), @remakes varchar(64), @ychgl float, @echgl float;
    declare @hg int, @pg int, @dm int, @bf int;
    declare @id varchar(64), @uid varchar(64), @value VARCHAR(10);
    -- Insert statements for procedure here
    --声明变量,存放当前已开启的事务数
    declare @exist_trancount int
    select @exist_trancount = @@trancount

    if @exist_trancount > 0
        --创建事务保存点
        save transaction tran_proc
    else
        --开启新事务
        begin transaction tran_proc
    
    
    -- 定义临时表
    if object_id('tempdb..#mypsum') is not null drop table #mypsum
    create table #mypsum(
            id int,pid varchar(64),moth varchar(64),weekday varchar(64),scdata datetime,zsdata datetime,jcdata datetime,
            num varchar(64),pmname varchar(64),prname varchar(64),prtype varchar(64),color varchar(64),ptpc varchar(64),car varchar(64),remakes varchar(64),
            ychgl float,echgl float,hg int,pg int,dm int,bf int
            )
    
    
    -- 定义游标.
    DECLARE c_test_main CURSOR FAST_FORWARD FOR
    SELECT distinct uid FROM wsd_count a where 
    a.count_reserve1 like case when @gh is not null and @gh!='' then @gh+'%' else a.count_reserve1 end 
    and a.count_reserve2 like case when @xm is not null and @xm!='' then @xm+'%' else a.count_reserve2 end 
    and a.line like case when @cx is not null and @cx!='' then @cx+'%' else a.line end 
    and a.num like case when @cp is not null and @cp!='' then @cp+'%' else a.num end
    and a.data >= case when @sdata is not null and @sdata!='' then @sdata else a.data end
    and a.data <= case when @edata is not null and @edata!='' then @edata else a.data end;
      -- 打开游标.
    OPEN c_test_main;
    
    WHILE 1=1
      BEGIN
        -- 填充数据.
        FETCH NEXT FROM c_test_main INTO @uid;
        -- 假如未检索到数据,退出循环.
        IF @@fetch_status!= 0 BREAK;
    
            -- 定义游标.
            DECLARE c_cp_main CURSOR FAST_FORWARD FOR
            SELECT distinct num FROM wsd_count a where a.uid=@uid 
            and a.line like case when @cx is not null and @cx!='' then @cx+'%' else a.line end 
            and a.num like case when @cp is not null and @cp!='' then @cp+'%' else a.num end
            and a.data >= case when @sdata is not null and @sdata!='' then @sdata else a.data end
            and a.data <= case when @edata is not null and @edata!='' then @edata else a.data end;
              -- 打开游标.
            OPEN c_cp_main;
    
            WHILE 1=1
              BEGIN
                -- 填充数据.
                FETCH NEXT FROM c_cp_main INTO @id;
                -- 假如未检索到数据,退出循环.
                IF @@fetch_status!= 0 BREAK;
    
                    SELECT
                        @moth = (select distinct a.count_reserve1 from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @weekday = (select distinct a.count_reserve2 from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @scdata = (select distinct b.scdata from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @zsdata = (select distinct b.zsdata from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @num = (select distinct b.num from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @pmname = (select distinct b.pmname from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @prname = (select distinct b.prname from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @prtype = (select distinct b.type from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @color = (select distinct b.color from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @ptpc = (select distinct b.ptlot from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @car = (select distinct b.car from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @remakes = (select distinct b.remake from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                        @hg = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '合格%'),
                        @pg = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '抛光%'),
                        @dm = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '打磨%'),
                        @bf = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '报废%');
                        if (@hg+@pg+@dm+@bf)>0
                            begin
                                select @echgl = (@hg+@pg*0.85)/(@hg+@pg+@dm+@bf) , @ychgl = cast(@hg as float)/(@hg+@pg+@dm+@bf) ,
                                 @ychgl =  Convert(decimal(20,4),@ychgl) , @echgl = Convert(decimal(20,4),@echgl);
                            end
                            else
                            begin
                                select @ychgl = 0,@echgl = 0;
                            end
    
                        INSERT INTO #mypsum ([pid],[moth],[weekday],[scdata],[zsdata],[jcdata],[num],[pmname],[prname],[prtype],[color],[ptpc],[car],[remakes],[ychgl],[echgl],[hg],[pg],[dm],[bf])
                        VALUES
                            (@id,@moth,@weekday ,@scdata,@zsdata,@jcdata,@num,@pmname,@prname,@prtype,@color,@ptpc,@car,@remakes,@ychgl,@echgl,@hg,@pg,@dm,@bf)
    
                END;
                -- 关闭游标
              CLOSE c_cp_main;
              -- 释放游标.
              DEALLOCATE c_cp_main;
    
      END;
    
      -- 关闭游标
      CLOSE c_test_main;
      -- 释放游标.
      DEALLOCATE c_test_main;
    
      select * from #mypsum;
    
      drop table #mypsum;
    

    if @@error<>0
    goto error

    if @exist_trancount = 0
        --提交事务
        commit tran tran_proc
        return 1
    error:
        --回滚事务或者事务保存点
        rollback transaction tran_proc
        return -1
    

    END

    
    
    点赞 打赏 评论
  • 如影随形小浩儿 2017-11-22 02:12

    这么说吧,数据量如果达到千万级以上的,实时查询的速度就会降下来,因此你要求的快速查询,就不能进行实时查询,数据体量过大的情况下
    建议你使用spark进行计算,然后再去取数据吧。

    点赞 打赏 评论
  • 而然 2017-11-29 11:20

    分表,建热点数据。 或者查询结果放缓存。

    点赞 打赏 评论

相关推荐 更多相似问题