gpt4 book ai didi

php - 选择具有多个值的记录并枚举它们

转载 作者:行者123 更新时间:2023-11-29 03:40:23 25 4
gpt4 key购买 nike

这是我的 table 现在的样子:

如您所见,有多个记录,如 XRAY。我想做的是列举那些像第一次X光第二次X光第三次X光

或任何类型的枚举。

顺便说一下我当前表的查询

"SELECT
`incurredcharges`.`procedure_no`,
`c`.`procedure`

FROM
incurredcharges
INNER JOIN (
SELECT `procedure`, `procedure_no` FROM `charges`
UNION ALL
SELECT `confinement`, `procedure_no` FROM `confinement`
UNION ALL
SELECT `service`, `procedure_no` FROM `ultrasound`
) c ON `incurredcharges`.`procedure_no` = c.`procedure_no`
WHERE `incurredcharges`.`patient_no` = '$id'";

最佳答案

虽然我不知道如何在前面加上“1st”、“2nd”等,但您可以在前面加上 1、2 等。这是一个示例:

SELECT
CONCAT(CASE WHEN cnt > 1 THEN CONCAT(RN,'-') ELSE '' END, t.Proc) Proc
FROM
(
SELECT
@curRow:=CASE WHEN @prevRow = a.Proc THEN @curRow+1 ELSE 1 END AS rn,
a.Proc,
@prevRow:=Proc grp
FROM (
SELECT Proc FROM Procedures ORDER BY Proc
) a JOIN (SELECT @curRow:=0) r
) t JOIN
(
SELECT Proc, COUNT(Proc) cnt
FROM Procedures
GROUP BY Proc
) c ON t.proc = c.proc

这里是 SQL Fiddle .

基本上,您需要将程序分组在一起,为每组指定一个行号。对于那些多于一条的记录,在行号前加上行号。

---编辑---

鉴于您的评论,您的查询只需要放在主 FROM 中(靠近中间),然后您需要将 procno 带到每个 select 语句中。尽管未经测试,但应该非常接近:

SELECT
procno,
CONCAT(CASE WHEN cnt > 1 THEN CONCAT(RN,'-') ELSE '' END, t.Proc) Proc
FROM
(
SELECT
@curRow:=CASE WHEN @prevRow = a.Proc THEN @curRow+1 ELSE 1 END AS rn,
a.Proc,
a.Procno,
@prevRow:=Proc grp
FROM (
SELECT
`incurredcharges`.`procedure_no` procno,
`c`.`procedure` proc
FROM
incurredcharges
INNER JOIN (
SELECT `procedure`, `procedure_no` FROM `charges`
UNION ALL
SELECT `confinement`, `procedure_no` FROM `confinement`
UNION ALL
SELECT `service`, `procedure_no` FROM `ultrasound`
) c ON `incurredcharges`.`procedure_no` = c.`procedure_no`
WHERE `incurredcharges`.`patient_no` = '$id'
ORDER BY `c`.`procedure`
) a
JOIN (SELECT @curRow:=0) r
) t JOIN
(
SELECT
`incurredcharges`.`procedure_no` procno,
`c`.`procedure` proc, Count(*) cnt
FROM
incurredcharges
INNER JOIN (
SELECT `procedure`, `procedure_no` FROM `charges`
UNION ALL
SELECT `confinement`, `procedure_no` FROM `confinement`
UNION ALL
SELECT `service`, `procedure_no` FROM `ultrasound`
) c ON `incurredcharges`.`procedure_no` = c.`procedure_no`
WHERE `incurredcharges`.`patient_no` = '$id'
GROUP BY `incurredcharges`.`procedure_no`,
`c`.`procedure`
) c ON t.proc = c.proc

祝你好运。

关于php - 选择具有多个值的记录并枚举它们,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14861282/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com