在一个查询中回答 [英] answer in a single query

问题描述

表名称:医院
--------

2. 2列

一世. PID(患者ID)
ii. PType(患者类型)

3.已插入4条记录

PID PType
--- -----
P1 OP1
P1 OP2
P1 IP1
P1 IP2


4.查询针对"P1"，那里有多少个OP和IP.

即，答案必须通过以下方式

OPTotal IPTotal
------- -------
2 2

提示:我像运算符"OP％"和"IP％"一样使用，单个搜索有效，但不能组合使用.

Table Name : Hospital
--------

2. 2 Columns

i. PID (Patient ID)
ii. PType (Patient Type)

3. 4 record(s) Inserted

PID PType
--- -----
P1 OP1
P1 OP2
P1 IP1
P1 IP2


4. Query is Against ''P1'', how many total numbers of OPs and IPs are there.

i.e, Answer must come in the following below way

OPTotal IPTotal
------- -------
2 2

Hint : I used like operator ''OP%'' and ''IP%'', individual searching is working but not combinedly.

推荐答案

Try

Try

SELECT
(SELECT COUNT(PType) FROM Hospital WHERE PID = 'P1' AND PType LIKE 'OP%') AS OPTotal,
(SELECT COUNT(PType) FROM Hospital WHERE PID = 'P1' AND PType LIKE 'IP%') AS IPTotal

SELECT SUM(CASE WHEN LEFT(ptype,2) = 'IP' THEN 1 ELSE 0 END) AS IP,
		SUM(CASE WHEN LEFT(ptype,2) = 'OP' THEN 1 ELSE 0 END) AS OP
FROM hospital
--output
IP	OP
2	2


SELECT COUNT(*),LEFT(ptype,2) from hospital where pid = 'P1' group by  LEFT(ptype,2)

--output
2	IP
2	OP

这篇关于在一个查询中回答的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！