Mysql

SQL - 計算具有相同值的行

  • March 7, 2021

我想分組並列出所有具有相同值的行（gruppo_muscolare.nome）併計算有多少值（esercizio.nome）與相同的值相關，但是如果我使用 GROUP BY 那麼我只會得到單個結果沒有完整列表。

SELECT
gruppo_muscolare.nome,
esercizio.nome,
COUNT(gruppo_muscolare.nome) AS counter
FROM tabella_allenamento, scheda, esercizio_scheda, esercizio, gruppo_muscolare
WHERE tabella_allenamento.id = 29 AND scheda.id_tabella=tabella_allenamento.id
AND esercizio_scheda.id_scheda=scheda.id AND esercizio.id=esercizio_scheda.id_esercizio
AND gruppo_muscolare.id=esercizio.id_gruppo_muscolare
GROUP BY gruppo_muscolare.nome, esercizio.nome, 
gruppo_muscolare.id 
ORDER BY counter DESC

我得到：

nome                  nome                 counter 
pettorali          Chest Press               1
pettorali      incline press hammer          1
quadricipiti       Leg Curl                  1

雖然我想得到：

nome                  nome                 counter 
pettorali          Chest Press               2
pettorali      incline press hammer          2
quadricipiti       Leg Curl                  1

如果我使用只有一個值的 GROUP BY 語句：

SELECT gruppo_muscolare.nome,
esercizio.nome, 
COUNT(gruppo_muscolare.nome) AS counter 
FROM tabella_allenamento, scheda, esercizio_scheda, esercizio, gruppo_muscolare
WHERE tabella_allenamento.id = 29 AND scheda.id_tabella=tabella_allenamento.id
AND esercizio_scheda.id_scheda=scheda.id AND esercizio.id=esercizio_scheda.id_esercizio
AND gruppo_muscolare.id=esercizio.id_gruppo_muscolare
GROUP BY gruppo_muscolare.nome
ORDER BY counter DESC

然後我得到：

nome                  nome                 counter 
pettorali          Chest Press               2
quadricipiti       Leg Curl                  1

這是我想要的，但缺少結果。

如何列出所有結果並同時獲得正確的計數器來計算esercizio.nome每個結果的數量gruppo_muscolare.nome？

謝謝！

編輯：這是創建和填充所有表以便能夠執行和測試程式碼的 SQL。

   -- phpMyAdmin SQL Dump
-- version 5.0.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Creato il: Mar 07, 2021 alle 13:00
-- Versione del server: 10.4.11-MariaDB
-- Versione PHP: 7.4.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `pump_db`
--

-- --------------------------------------------------------

--
-- Struttura della tabella `esercizio`
--

CREATE TABLE `esercizio` (
 `id` int(11) NOT NULL,
 `nome` varchar(100) NOT NULL,
 `id_gruppo_muscolare` int(11) NOT NULL,
 `gruppo_muscolare` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `esercizio`
--

INSERT INTO `esercizio` (`id`, `nome`, `id_gruppo_muscolare`, `gruppo_muscolare`) VALUES
(1, 'incline press hammer', 1, 'pettorali'),
(2, 'Chest Press ', 1, 'pettorali'),
(3, 'Leg Curl', 2, 'quadricipiti'),
(4, 'spinte con manubri', 3, 'spalle'),
(5, 'Lat machine', 5, 'dorsali'),
(8, 'spalle', 3, 'incline press hammer'),
(10, 'Pulley con triangolo', 5, 'dorsali'),
(11, 'croci su panca piana', 1, 'pettorali');

-- --------------------------------------------------------

--
-- Struttura della tabella `esercizio_scheda`
--

CREATE TABLE `esercizio_scheda` (
 `id` int(11) NOT NULL,
 `id_esercizio` int(11) NOT NULL,
 `id_scheda` int(11) NOT NULL,
 `serie` varchar(50) NOT NULL,
 `ripetizioni` varchar(50) NOT NULL,
 `kg` varchar(50) NOT NULL,
 `note` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `esercizio_scheda`
--

INSERT INTO `esercizio_scheda` (`id`, `id_esercizio`, `id_scheda`, `serie`, `ripetizioni`, `kg`, `note`) VALUES
(38, 2, 26, '3', '2', '', ''),
(39, 1, 26, '1', '2', '', ''),
(40, 3, 26, '2', '3', '', ''),
(41, 11, 27, '2', '10-2-1', '', ''),
(42, 2, 27, '3', '2', '', ''),
(43, 1, 27, '1', '2', '', '');

-- --------------------------------------------------------

--
-- Struttura della tabella `gruppo_muscolare`
--

CREATE TABLE `gruppo_muscolare` (
 `id` int(11) NOT NULL,
 `nome` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `gruppo_muscolare`
--

INSERT INTO `gruppo_muscolare` (`id`, `nome`) VALUES
(1, 'pettorali'),
(2, 'quadricipiti'),
(3, 'spalle'),
(4, 'glutei'),
(5, 'dorsali'),
(6, 'bicipiti'),
(7, 'tricipiti'),
(8, 'adduttori'),
(9, 'abduttori'),
(10, 'addominali'),
(11, 'femorali');

-- --------------------------------------------------------

--
-- Struttura della tabella `personal_trainer`
--

CREATE TABLE `personal_trainer` (
 `id` int(11) NOT NULL,
 `nome` varchar(50) NOT NULL,
 `cognome` varchar(50) NOT NULL,
 `codice_pt` int(50) NOT NULL,
 `email` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `personal_trainer`
--

INSERT INTO `personal_trainer` (`id`, `nome`, `cognome`, `codice_pt`, `email`) VALUES
(1, 'Jacopo', 'Stefano', 1, ''),
(2, 'Federica', 'Metto', 2, ''),
(3, 'Paolo', 'Marsella', 33, '');

-- --------------------------------------------------------

--
-- Struttura della tabella `scheda`
--

CREATE TABLE `scheda` (
 `id` int(11) NOT NULL,
 `id_tabella` int(11) NOT NULL,
 `tipo` varchar(5) NOT NULL,
 `data` date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `scheda`
--

INSERT INTO `scheda` (`id`, `id_tabella`, `tipo`, `data`) VALUES
(26, 29, 'A', '2021-02-28'),
(27, 30, 'A', '2021-02-28');

-- --------------------------------------------------------

--
-- Struttura della tabella `tabella_allenamento`
--

CREATE TABLE `tabella_allenamento` (
 `id` int(11) NOT NULL,
 `id_utente` int(11) NOT NULL,
 `nome` varchar(100) NOT NULL,
 `pt` varchar(100) NOT NULL,
 `data` date NOT NULL DEFAULT current_timestamp(),
 `data_inizio` varchar(100) NOT NULL,
 `data_fine` varchar(100) NOT NULL,
 `recupero_serie` varchar(100) NOT NULL,
 `recupero_esercizio` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `tabella_allenamento`
--

INSERT INTO `tabella_allenamento` (`id`, `id_utente`, `nome`, `pt`, `data`, `data_inizio`, `data_fine`, `recupero_serie`, `recupero_esercizio`) VALUES
(29, 1000, 'Rocco Galati #1000-2021-02-28-11-38-07', 'Jacopo Stefano', '2021-02-28', '28-02-2021', '30-03-2021', '1:30\"', '1:30\"'),
(30, 1000, 'Rocco Galati #1000-2021-02-28-11-39-16', 'Jacopo Stefano', '2021-02-28', '28-02-2021', '30-03-2021', '1:30\"', '1:30\"');

-- --------------------------------------------------------

--
-- Struttura della tabella `utente`
--

CREATE TABLE `utente` (
 `id` int(255) NOT NULL,
 `nome` varchar(100) NOT NULL,
 `cognome` varchar(100) NOT NULL,
 `cf` varchar(100) NOT NULL,
 `peso` float NOT NULL,
 `altezza` float NOT NULL,
 `note` varchar(500) NOT NULL,
 `email` varchar(50) NOT NULL,
 `data_di_nascita` varchar(50) NOT NULL,
 `telefono` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `utente`
--

INSERT INTO `utente` (`id`, `nome`, `cognome`, `cf`, `peso`, `altezza`, `note`, `email`, `data_di_nascita`, `telefono`) VALUES
(1000, 'Rocco', 'Galati', 'GLTRCC84R04E825L', 80, 178, 'Nessuna nota', 'roccogalati@gmail.com', '04/10/1984', '3280613637'),
(1002, 'Marco', 'Rossi', 'GLTRCC85R04E815L', 90, 180, 'nessuna nota particolare', 'info@robo-dyne.com', '04/10/1985', '3280613637');

--
-- Indici per le tabelle scaricate
--

--
-- Indici per le tabelle `esercizio`
--
ALTER TABLE `esercizio`
 ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `esercizio_scheda`
--
ALTER TABLE `esercizio_scheda`
 ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `gruppo_muscolare`
--
ALTER TABLE `gruppo_muscolare`
 ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `personal_trainer`
--
ALTER TABLE `personal_trainer`
 ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `scheda`
--
ALTER TABLE `scheda`
 ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `tabella_allenamento`
--
ALTER TABLE `tabella_allenamento`
 ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `utente`
--
ALTER TABLE `utente`
 ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT per le tabelle scaricate
--

--
-- AUTO_INCREMENT per la tabella `esercizio`
--
ALTER TABLE `esercizio`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT per la tabella `esercizio_scheda`
--
ALTER TABLE `esercizio_scheda`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=44;

--
-- AUTO_INCREMENT per la tabella `gruppo_muscolare`
--
ALTER TABLE `gruppo_muscolare`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT per la tabella `personal_trainer`
--
ALTER TABLE `personal_trainer`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT per la tabella `scheda`
--
ALTER TABLE `scheda`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT per la tabella `tabella_allenamento`
--
ALTER TABLE `tabella_allenamento`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT per la tabella `utente`
--
ALTER TABLE `utente`
 MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1007;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

不要使用GROUP BY子句，而是將該COUNT()函式用作視窗函式，這樣您就可以像這樣返回所有行：

SELECT
gruppo_muscolare.nome,
esercizio.nome,
COUNT(esercizio.nome) OVER (PARTITION BY 
gruppo_muscolare.nome) AS counter
FROM tabella_allenamento, scheda, esercizio_scheda, esercizio, 
gruppo_muscolare
WHERE tabella_allenamento.id = 29 AND 
scheda.id_tabella=tabella_allenamento.id
AND esercizio_scheda.id_scheda=scheda.id AND 
esercizio.id=esercizio_scheda.id_esercizio
AND gruppo_muscolare.id=esercizio.id_gruppo_muscolare
ORDER BY counter DESC

引用自：https://dba.stackexchange.com/questions/286617

相關問答

Mysql

如何獲得 3 個子計數查詢的總和並使用屬性對它們進行分組

  • March 11, 2022
檢視問答
Mysql

SELECT 顯示兩個 COUNT

  • January 16, 2014
檢視問答
Mysql

MySQL COUNT 和 GROUP BY 顯示多個項目

  • May 26, 2022
檢視問答
Mysql

MySQL - 通過汽車製造獲取汽車模型的計數

  • January 3, 2022
檢視問答
Mysql

如何將 GROUP_CONCAT 用於列和 COUNT(*)？

  • December 15, 2021
檢視問答
Mysql

mysql：計算每天開放的門票數量

  • March 30, 2021
檢視問答