为什么在SQL查询中会出现错误?

I made a query from view table in phpMyAdmin. When I tried to do query, the result was error. Here is my query:

我在phpMyAdmin中做了一个查询。当我尝试查询时，结果是错误。这是我的查询:

SELECT TahunMasuk, 
  COUNT(CASE WHEN `JenisKelamin`=`Perempuan`) AS `Perempuan`,
  COUNT(CASE WHEN `JenisKelamin`=`Laki-laki`) AS `Laki-laki`
FROM jeniskelaminreal
GROUP BY TahunMasuk;

The error message is

错误信息是

#1054 - Unknown column 'Perempuan' in 'field list'

#1054 -未知栏“Perempuan”在“字段列表”中

I use left join in my view table. Here is my view table named jeniskelaminreal

我在视图表中使用左连接。这是我的视图表jeniskelaminreal。

What may I do so that I can get the result? Thank you in advance

我该怎么做才能得到结果?提前谢谢你

6 个解决方案

#1

CASE statement is missing ELSE part and back quote is also a problem, so it should be like this.

CASE语句缺少ELSE部分，后面引用也是一个问题，所以应该是这样的。

SELECT TahunMasuk, 
  COUNT(CASE WHEN `JenisKelamin`='Perempuan' THEN 1 END) AS `Perempuan`,
  COUNT(CASE WHEN `JenisKelamin`='Laki-laki' THEN 1 END) AS `Laki-laki`
FROM jeniskelaminreal
GROUP BY TahunMasuk;

#2

You are using backticks instead of double quotes while comparing the value. Also, your CASE statements are missing THEN and END, try the following:

在比较值时，您使用的是backticks，而不是双引号。此外，您的CASE语句也会丢失，请尝试以下内容:

SELECT TahunMasuk, 
  COUNT(CASE WHEN `JenisKelamin`='Perempuan' THEN 1 END) AS 'Perempuan',
  COUNT(CASE WHEN `JenisKelamin`='Laki-laki' THEN 1 END) AS 'Laki-laki'
FROM jeniskelaminreal
GROUP BY TahunMasuk;

#3

A case has the following syntax:

一个案例有以下语法:

COUNT(CASE WHEN `JenisKelamin`= 'Perempuan' THEN 1 ELSE null END) AS `Perempuan`

or since the else part is null by default:

或者因为默认情况下，else部分为null:

COUNT(CASE WHEN `JenisKelamin`= 'Perempuan' THEN 1 END) AS `Perempuan`

In MySQL you could also do

在MySQL中也可以。

SUM(JenisKelamin = 'Perempuan') AS `Perempuan`

Also string are delimited by quotes and not backticks.

同样，字符串被引号分隔，而不是返回。

#4

You are missing the THEN and END keywords. Also, the string constants need to be enclosed in single quotes:

您缺少的是THEN和END关键字。另外，字符串常量需要用单引号括起来:

SELECT TahunMasuk, 
       COUNT(CASE WHEN JenisKelamin = 'Perempuan' THEN 1 END) AS `Perempuan`,
       COUNT(CASE WHEN JenisKelamin = 'Laki-laki' THEN 1 END) AS `Laki-laki`
FROM jeniskelaminreal
GROUP BY TahunMasuk;

MySQL (which you appear to be using) has a convenient shorthand:

MySQL(您似乎正在使用)有一个方便的简写:

SELECT TahunMasuk, 
       SUM(CJenisKelamin = 'Perempua') AS Perempuan,
       SUM(JenisKelamin = 'Laki-laki') AS `Laki-laki`
FROM jeniskelaminreal
GROUP BY TahunMasuk;

#5

Replace ` with ' in 'Perempuan' and 'Laki-laki'

用“Perempuan”和“Laki-laki”代替“with”

SELECT TahunMasuk, 
  COUNT(CASE WHEN `JenisKelamin`='Perempuan' THEN 1 END) AS 'Perempuan',
  COUNT(CASE WHEN `JenisKelamin`='Laki-laki' THEN 1 END) AS 'Laki-laki'
FROM jeniskelaminreal
GROUP BY TahunMasuk;

#6

as per mysql documentation the syntax is

根据mysql文档，语法是。

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE

或

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

READ HERE

在这里阅读

your query look like this

您的查询是这样的。

SELECT TahunMasuk, 
COUNT(CASE WHEN `JenisKelamin`='Perempuan' THEN 1 END) AS 'Perempuan',
COUNT(CASE WHEN `JenisKelamin`='Laki-laki' THEN 1 END) AS 'Laki-laki'
FROM jeniskelaminreal
GROUP BY TahunMasuk;

and also you can do this way by using field() with sum() to count those results

您也可以通过使用sum()来计算这些结果。

SELECT TahunMasuk, 
SUM(FIELD(JenisKelamin,Perempuan)) AS 'Perempuan',
SUM(FIELD(JenisKelamin,Laki-laki)) AS 'Laki-laki'
FROM jeniskelaminreal
GROUP BY TahunMasuk;

#1