ms access 2016中的sql查询

h9a6wy2h 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(562)

我正在使用ms access 2016。我的查询结果如下：

我需要如下结果：

我的问题是：

SELECT IIf([Posisi]="Supervisor",[Nm_Karyawan]) AS Supervisor, IIf([Divisi]="Dept001" And [SubDivisi]="Operator",[Nm_Karyawan]) AS Printing, IIf([Divisi]="Dept001" And [SubDivisi]="Tinta",[Nm_Karyawan]) AS Tinta, IIf([Divisi]="Dept001" And [SubDivisi]="Cylinder",[Nm_Karyawan]) AS Cylinder, IIf([Divisi]="Dept003" And [SubDivisi]="Operator",[Nm_Karyawan]) AS Dry, IIf([Divisi]="Dept002" And [SubDivisi]="Operator",[Nm_Karyawan]) AS Extrusion, IIf([Divisi]="Dept004" And [SubDivisi]="Operator",[Nm_Karyawan]) AS Slitting, IIf([Divisi]="Dept005" And [SubDivisi]="Operator",[Nm_Karyawan]) AS Rewind
FROM T_User1
WHERE (((T_User1.Group)="001"))
GROUP BY IIf([Posisi]="Supervisor",[Nm_Karyawan]), IIf([Divisi]="Dept001" And [SubDivisi]="Operator",[Nm_Karyawan]), IIf([Divisi]="Dept001" And [SubDivisi]="Tinta",[Nm_Karyawan]), IIf([Divisi]="Dept001" And [SubDivisi]="Cylinder",[Nm_Karyawan]), IIf([Divisi]="Dept003" And [SubDivisi]="Operator",[Nm_Karyawan]), IIf([Divisi]="Dept002" And [SubDivisi]="Operator",[Nm_Karyawan]), IIf([Divisi]="Dept004" And [SubDivisi]="Operator",[Nm_Karyawan]), IIf([Divisi]="Dept005" And [SubDivisi]="Operator",[Nm_Karyawan]);

这是我的table：

如有需要，请帮助我查询结果。谢谢您。
你好，费尔南多

sql ms-access grouping sorting

来源：https://stackoverflow.com/questions/62912892/query-in-ms-access-2016

1条答案

按热度按时间

nzk0hqpo1#

你可以使用（假）聚合函数

SELECT IIf([Posisi]="Supervisor",[Nm_Karyawan]) AS Supervisor
, min(IIf([Divisi]="Dept001" And [SubDivisi]="Operator",[Nm_Karyawan])) AS Printing
, min(IIf([Divisi]="Dept001" And [SubDivisi]="Tinta",[Nm_Karyawan])) AS Tinta
, min(IIf([Divisi]="Dept001" And [SubDivisi]="Cylinder",[Nm_Karyawan])) AS Cylinder
, min(IIf([Divisi]="Dept003" And [SubDivisi]="Operator",[Nm_Karyawan])) AS Dry
, min(IIf([Divisi]="Dept002" And [SubDivisi]="Operator",[Nm_Karyawan])) AS Extrusion
, min(IIf([Divisi]="Dept004" And [SubDivisi]="Operator",[Nm_Karyawan])) AS Slitting
, min(IIf([Divisi]="Dept005" And [SubDivisi]="Operator",[Nm_Karyawan])) AS Rewind
FROM T_User1
WHERE (((T_User1.Group)=[Forms]![F_Jadwal1]![GrpShift1]))
GROUP BY IIf([Posisi]="Supervisor",[Nm_Karyawan])

ORDER BY IIf([Posisi]="Supervisor",[Nm_Karyawan]);

对于空值，请尝试使用nz（）

SELECT IIf([Posisi]="Supervisor",[Nm_Karyawan]) AS Supervisor
    , max(IIf([Divisi]="Dept001" And [SubDivisi]="Operator",Nz([Nm_Karyawan],''))) AS Printing
    , max(IIf([Divisi]="Dept001" And [SubDivisi]="Tinta",Nz([Nm_Karyawan],''))) AS Tinta
    , max(IIf([Divisi]="Dept001" And [SubDivisi]="Cylinder",Nz([Nm_Karyawan],''))) AS Cylinder
    , max(IIf([Divisi]="Dept003" And [SubDivisi]="Operator",Nz([Nm_Karyawan],''))) AS Dry
    , max(IIf([Divisi]="Dept002" And [SubDivisi]="Operator",Nz([Nm_Karyawan],''))) AS Extrusion
    , max(IIf([Divisi]="Dept004" And [SubDivisi]="Operator",Nz([Nm_Karyawan],''))) AS Slitting
    , max(IIf([Divisi]="Dept005" And [SubDivisi]="Operator",Nz([Nm_Karyawan],''))) AS Rewind
    FROM T_User1
    WHERE (((T_User1.Group)=[Forms]![F_Jadwal1]![GrpShift1]))
    GROUP BY IIf([Posisi]="Supervisor",[Nm_Karyawan])

    ORDER BY IIf([Posisi]="Supervisor",[Nm_Karyawan]);

赞(0）回复(0）举报 2021-07-26

我来回答

ms access 2016中的sql查询

1条答案

相关问题

热门标签

最新问答