Hallo Leute. Ich beschäftige mich seit neuerem mit MySql.. Ich möchte gerne eine Iterative Abfrage erstellen .. Es geht darum, Scan´s von Kunden Monatlich aufzusummieren.. Ich hab es selber als Abfrage hinbekommen , jedoch musste ich immer wieder alle Monatsabrechnung neu abfragen.. Kann mir jemand helfen?
Iterative Abfrage-MySQL
- Ersteller cs3838
- Erstellt am
Werbung:
akretschmer
Datenbank-Guru
- Beiträge
- 10.372
Zeig, was Du an Tabellen und Daten hast, was herauskommen soll und was Du versucht hast bisher.
Erst Mal ein riesen danke schön dass du mir hilst 
Code:
SELECT `u`.`id` AS `Id` , `u`.`businessName` AS `BusinessName` , `u`.`createdOn` AS `AktiviertSeit` ,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND DATE_ADD(`u`.`createdOn` ,Interval 1 month) > `scancode`.`scanDateTime`
) AS `ScansMonat 1` ,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 2 month) > `scancode`.scanDateTime) - (DATE_ADD(`u`.`createdOn` ,Interval 1 month) > `scancode`.scanDateTime)
) AS `ScansMonat 2`
,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 3 month) > `scancode`.`scanDateTime`) - (DATE_ADD(`u`.`createdOn` ,Interval 2 month) > `scancode`.`scanDateTime`)
) AS `ScansMonat 3` ,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 4 month) > `scancode`.`scanDateTime`) - (DATE_ADD(`u`.`createdOn` ,Interval 3 month) > `scancode`.`scanDateTime`)
) AS `ScansMonat 4`,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 5 month) > `scancode`.`scanDateTime`)-(DATE_ADD(`u`.`createdOn` ,Interval 4 month) > `scancode`.`scanDateTime`)
) AS `ScansMonat 5`,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 6 month) > `scancode`.`scanDateTime`) - (DATE_ADD(`u`.`createdOn` ,Interval 5 month) > `scancode`.`scanDateTime`)
) AS `ScansMonat 6` ,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 7 month) > `scancode`.`scanDateTime`) - (DATE_ADD(`u`.`createdOn` ,Interval 6 month) > `scancode`.`scanDateTime`)
) AS `ScansMonat 7`
FROM `qrsjetty`.`user` `u`
WHERE `u`.`id` = 119
OR `u`.`id` = 29
ORDER BY `u`.`createdOn` ASC
Code:
SELECT `u`.`id` AS `Id` , `u`.`businessName` AS `BusinessName` , `u`.`createdOn` AS `AktiviertSeit` ,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND DATE_ADD(`u`.`createdOn` ,Interval 1 month) > `scancode`.`scanDateTime`
) AS `ScansMonat 1` ,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 2 month) > `scancode`.scanDateTime) - (DATE_ADD(`u`.`createdOn` ,Interval 1 month) > `scancode`.scanDateTime)
) AS `ScansMonat 2`
,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 3 month) > `scancode`.`scanDateTime`) - (DATE_ADD(`u`.`createdOn` ,Interval 2 month) > `scancode`.`scanDateTime`)
) AS `ScansMonat 3` ,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 4 month) > `scancode`.`scanDateTime`) - (DATE_ADD(`u`.`createdOn` ,Interval 3 month) > `scancode`.`scanDateTime`)
) AS `ScansMonat 4`,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 5 month) > `scancode`.`scanDateTime`)-(DATE_ADD(`u`.`createdOn` ,Interval 4 month) > `scancode`.`scanDateTime`)
) AS `ScansMonat 5`,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 6 month) > `scancode`.`scanDateTime`) - (DATE_ADD(`u`.`createdOn` ,Interval 5 month) > `scancode`.`scanDateTime`)
) AS `ScansMonat 6` ,
(
SELECT count( `qrsjetty`.`scancode`.`id` )
FROM `qrsjetty`.`scancode`
WHERE `qrsjetty`.`scancode`.`barCode_id`
IN (
SELECT `qrsjetty`.`barcode`.`id`
FROM `qrsjetty`.`barcode`
WHERE (
`qrsjetty`.`barcode`.`clientAdminUser_id`=`u`.`id`
)
)
AND (DATE_ADD(`u`.`createdOn` ,Interval 7 month) > `scancode`.`scanDateTime`) - (DATE_ADD(`u`.`createdOn` ,Interval 6 month) > `scancode`.`scanDateTime`)
) AS `ScansMonat 7`
FROM `qrsjetty`.`user` `u`
WHERE `u`.`id` = 119
OR `u`.`id` = 29
ORDER BY `u`.`createdOn` ASC
Also es soll das rauskommen was ich als code gepostet habe.. Jedoch soll die Abfrage iterativ sein..
akretschmer
Datenbank-Guru
- Beiträge
- 10.372
Sorry, aber das sieht gruselig aus. Und ich sehe auch nicht die Definition Deiner Tabellen und auch keine Beispieldaten. Und raus kommen soll sicher nicht so ein gruseliges Statement, sondern irgend welche Zahlen. Und ja: es gibt auch Code-Tags, um sowas in lesbar hier zu posten.
Werbung:
