代码之家  ›  专栏  ›  技术社区  ›  hud

从SQL中的查询中获取所有员工

  •  1
  • hud  · 技术社区  · 9 年前

    我有一个查询,它提供了 Comp_mkey = 7 and 110 。查询如下

    SELECT DISTINCT m.comp_mkey,
                c.company_name,
                m.start_date,
                m.End_date,
                m.Process_date,
                m.Months_days,
                m.Process_year,
                m.Process_Month FROM emp_mst e,
                company_mst c,
                P_Monthly_Must_Para_Hdr m WHERE c.mkey = e.comp_mkey
                AND m.comp_mkey = e.comp_mkey
                AND (m.process_date IS NOT NULL
                AND (convert(varchar,(getDate()),103)) = convert(varchar,m.process_date + 1, 103))
                AND m.Process_year = 2016
                AND Process_month = 2
    

    查询的输出如下:-

    Table output

    现在我想要的是,

    所有员工姓名 comp_mkey 在中 7, 110

    请告诉我怎么做

    我正在使用 SQL-server 2008

    1 回复  |  直到 9 年前
        1
  •  2
  •   Matt    9 年前

    只为名字

    SELECT DISTINCT e.Emp_name
    FROM emp_mst e
    WHERE e.comp_mkey IN ('7', '110')
    

    就像你说的使用 IN

    SELECT DISTINCT m.comp_mkey, c.company_name, m.start_date, m.End_date, m.Process_date, m.Months_days, m.Process_year, m.Process_Month 
    FROM emp_mst e, company_mst c, P_Monthly_Must_Para_Hdr m 
    WHERE c.mkey = e.comp_mkey
    AND m.comp_mkey = e.comp_mkey
    AND (m.process_date IS NOT NULL
    AND (convert(varchar,(getDate()),103)) = convert(varchar,m.process_date + 1, 103))
    AND m.Process_year = 2016
    AND Process_month = 2
    AND m.comp_mkey IN ('7', '110')
    

    此外,您应该使用这样的显式连接。

    SELECT DISTINCT m.comp_mkey, c.company_name, m.start_date, m.End_date, m.Process_date, m.Months_days, m.Process_year, m.Process_Month 
    FROM emp_mst e
    INNER JOIN company_mst c ON c.mkey = e.comp_mkey
    INNER JOIN P_Monthly_Must_Para_Hdr m ON m.comp_mkey = e.comp_mkey
    WHERE (m.process_date IS NOT NULL
    AND (convert(varchar,(getDate()),103)) = convert(varchar,m.process_date + 1, 103))
    AND m.Process_year = 2016
    AND Process_month = 2
    AND m.comp_mkey IN ('7', '110')