Outils pour utilisateurs
sql:start
Différences
Ci-dessous, les différences entre deux révisions de la page.
| Prochaine révision | Révision précédente | ||
|
sql:start [2024/03/06 20:33] jce créée |
— (Version actuelle) | ||
|---|---|---|---|
| Ligne 1: | Ligne 1: | ||
| - | ====== Exemples de requêtes sql ====== | ||
| - | |||
| - | ===== Membres ===== | ||
| - | |||
| - | ===== Compta ===== | ||
| - | |||
| - | ===== Dons ===== | ||
| - | * Membres qui sont inscrits à une activité sans avoir d' | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | membres.nom, | ||
| - | membres.email, | ||
| - | membres.id AS _user_id, | ||
| - | s.label AS ' | ||
| - | f.label AS ' | ||
| - | su.paid AS ' | ||
| - | su.date AS ' | ||
| - | FROM membres | ||
| - | INNER JOIN services_users su ON su.id_user = membres.id | ||
| - | INNER JOIN services s ON s.id = su.id_service | ||
| - | INNER JOIN services_fees f ON f.id = su.id_fee | ||
| - | WHERE s.id != 1 AND su.date >= s.start_date | ||
| - | AND su.id_user NOT IN (SELECT id_user FROM services_users sus WHERE sus.id_service = 1 AND sus.date >= s.start_date) | ||
| - | ORDER BY membres.nom ASC LIMIT 1000 | ||
| - | ; | ||
| - | </ | ||
| - | |||
| - | * liste des adhérents ayant versé une cotisation ou un don à l’association ; voir [[https:// | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | su.date AS 'Date d'' | ||
| - | su.expiry_date AS 'Date d'' | ||
| - | SUM(tl.debit)/ | ||
| - | expected_amount - SUM(tl.debit)/ | ||
| - | users.*, | ||
| - | users.id AS _user_id | ||
| - | FROM users | ||
| - | INNER JOIN ( | ||
| - | SELECT MAX(expiry_date) AS expiry_date, | ||
| - | date, id_user, id, expected_amount | ||
| - | FROM services_users | ||
| - | -- Modifier ici l'ID de l' | ||
| - | WHERE id_service = 1 AND paid = 1 | ||
| - | AND (expiry_date >= date() OR expiry_date IS NULL) | ||
| - | GROUP BY id_user) AS su | ||
| - | ON su.id_user = users.id | ||
| - | LEFT JOIN acc_transactions_users tu | ||
| - | ON tu.id_service_user = su.id | ||
| - | LEFT JOIN acc_transactions_lines tl | ||
| - | ON tl.id_transaction = tu.id_transaction | ||
| - | GROUP BY users.id | ||
| - | </ | ||
sql/start.1709753630.txt.gz · Dernière modification: 2024/03/06 20:33 de jce
Outils de la page
